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
unknown	date	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-03	2024-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