Solved: How to round datetime into 1/24 - Page 2

rohit_shoppertr · 03-30-2022 12:17 AM

Hi team,

I have a date column which has value like 2022-03-29 23:00:00. I need to round it up by 1/24.

the expression should be round(2022-03-29 23:00:00,1/24)

For example in other tool it works as:

Round(3.88875,1/1000)

Returns 3.889

In this example, the size of the step is 0.001, which rounds the number up and limits it to three decimal places.

Can you please tell me how to do it in Looker?

dbrinegar

I’m not sure there’s an equivalent to round timestamps in BQ that you might have seen on Qlik, etc.
This flow does round “Up” to the next hour if the timestamp is closer, by adding 30 minutes to the timestamp and flooring the hour:

timestamp_trunc(timestamp_add(timestamp_to_round, interval 30 minute), hour)

If the timestamp is 00:30:00 or less, the output of the formula will return a timestamp that is truncated to the hour of the original timestamp value.
As an example, if we pass through 01:20:00:
Adding 30 minutes to the timestamp produces 1:40:00,
Then we truncate the hour value and receive 01:00:00.

If we submit 01:45:00:
Adding 30 minutes to the timestamp produces 2:15:00,
Then we truncate the hour value and receive 2:00:00.

The formula and interval to add would have to be adjusted to whatever scope you’re rounding to, this particular formula would produce similar results to round(timestamp,1/24). You could use this same process for other intervals, but the logic would have more than one parameter. If you wanted to round to 30 minute intervals you could add 15 Minutes to the source timestamp and floor to ½ hour intervals.

An alternative could be to convert to unixtime, cast to numeric, round to your desired microsecond level, and cast back to timestamp.

View solution in original post