ClickHouse date format

[karthi djax]

Am having a DateTime column with value as 2022-03-28 09:15:00, would like to get get result from query as start as 2022-03-28 09:00:00 and end as 2022-03-28 09:59:59. Can you help me to get it resolved.

For start found toStartOfHour, is there any option for end of hour.

Thanks in Advance

Regards,

Karthik 

[Ivan Blinkov]

Hi Karthik,

It sounds like to calculate end you could get that start of hour, add one hour to it and then subtract one second? There are multiple ways to do it, for example there are these functions: 

https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions/#addyears-addmonths-addweeks-adddays-addhours-addminutes-addseconds-addquarters

--

Ivan

--
You received this message because you are subscribed to the Google Groups "ClickHouse" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clickhouse+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clickhouse/c2a80652-aa97-4775-9684-18c65e58e3ban%40googlegroups.com.

--

--

Ivan Blinkov

[karthi djax]

Hi Ivan,

Thanks for the details, done with solution as below,

select DISTINCT date_time, addHours(date_time, 1) AS next_hour, date_sub(SECOND, 1, next_hour) AS end_time from date_check;

Thanks again

[karthi djax]

Aldo this works

select DISTINCT date_time, date_sub(SECOND, 1, addHours(date_time, 1)) AS end_time from ox_data_summary_ad_hourly_cron;