Date and time

Date and time#

Working with dates and times is quite common when using SQL. Since date columns have many specific features, there is a dedicated section for handling them.

Date#

All database systems have some types that describe jsut a date - it’s a date without time.

As an example, consider how the DATE data type can be defined in Postgres. In the following cell, the types of the results from different ways of creating a date are printed.

--postgreSQL
SELECT 
    pg_typeof('2999-08-12') one,
    pg_typeof(CAST('2999-08-12' AS DATE)) two,
    pg_typeof('2999-08-12'::DATE) three,
    pg_typeof(to_date('2999-08-12', 'YYYY-MM-DD')) four
;

SELECT 1
one two three four
unknowndate date date

In ClickHouse, dates are stored as strings. By using the toDate function, you can make ClickHouse interpret them as datetime columns.

--ClickHouse
SELECT
    toTypeName('2999-12-23'),
    toTypeName(toDate('2999-12-23'));
toTypeName('2999-12-23') toTypeName(toDate('2999-12-23'))
String Date

Datetime/Timestamp#

The Datetime type is used to describe date and time with precision.

The following cell demonstrates a comparison of typecasting to DATE and TIMESTAMP in postgres.

--postgreSQL
SELECT 
    '2024-07-03 14:30:57.902629'::DATE,
    '2024-07-03 14:30:57.902629'::TIMESTAMP;

SELECT 1
date timestamp
2024-07-032024-07-03 14:30:57.902629

So in case DATE there is only infomation about day but in TMESTAMP case we’re getting accuracy up to microsecond.

The same example applies to ClickHouse.

--ClickHouse
SELECT
    toDate('2024-07-03 14:30:57'),
    toDateTime64('2024-07-03 14:30:57.902629', 6);
toDate('2024-07-03 14:30:57') toDateTime64('2024-07-03 14:30:57.902629', 6)
2024-07-03 2024-07-03 14:30:57.902629
Contents