How to round datetime into 1/24

[rohit_shoppertr]

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?

[rohit_shoppertr]

@Dawid Can you please add your input for this thread?

[Dawid]

What do you mean you want to round up a datetime by 1/24? In your example you have 3.8875, is that some kind of date to integer conversion?

ROUND functions take two arguments: FLOAT64 and INTEGER, so you need to make sure your data matches those types

[rohit_shoppertr]

I have a field which has timestamp as 2022-03-29 23:00:00.

I want to make it round to hour level as round(‘2022-03-29 23:00:00’,1/24).

So if I show you how result changes after apply round:

After apply round result will be:

So this helps us to map value between two fields on which join is applied. 

Same thing I want to do in Looker with  BQ, round the timestamp by 1/24

[dbrinegar]

For BQ, rounding to the nearest hour could be done with:

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

For example:

with demo_times as (
          select timestamp('2022-03-29 23:00:00') t 
union all select timestamp('2022-03-29 23:15:00') 
union all select timestamp('2022-03-29 23:45:00'))
select d.t
     , timestamp_trunc(timestamp_add(d.t, interval 30 minute), hour) t_rounded
from demo_times d

Produces: 

t	t_rounded
2022-03-29T23:00:00.000+00:00	2022-03-29T23:00:00.000+00:00
2022-03-29T23:15:00.000+00:00	2022-03-29T23:00:00.000+00:00
2022-03-29T23:45:00.000+00:00	2022-03-30T00:00:00.000+00:00

If you just want to remove the minutes/seconds after the hour, timestamp_trunc would be a good solution.

[rohit_shoppertr]

Isn’t 2022-03-29T23:45:00.000+00:00  should be rounded to 2022-03-30T00:00:00.000+00:00 because next hour start time is the closed to 23:45 hours?

 I think timestamp_trunc is just waving off minutes parts.

Let me share one example: round(2,6 ), here 2 is the value and 6 is the step.

Returns 0. Rounded down because 2 is less than half of the step interval of 6.

In this example, the size of the step is 6 and the base of the step interval is 0.

The intervals are ...0 <= x <6, 6 <= x <12, 12<= x <18…

I want to the same thing, round(timestamp, step of round)

=timestamp(round('2022-03-29 23:30:00',1/24)) is equal to 

[dbrinegar · Accepted solution]

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.

[rohit_shoppertr]

One last question,

On the field I am trying to apply your solution, it is an auto generated code:

  dimension_group: Date{
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    datatype: datetime
    sql: ${TABLE}.Date ;;
  }

Why do there are two definition of data and datatype?type: time and     datatype: datetime?

what is the difference between “type: time” and  “datatype: datetime”?

[dbrinegar]

The “type” influences more how the results interact with the Looker explore. (Type can affect the filter options, etc). It can also influence the SQL Generated [eg: CAST( x AS TIMESTAMP) in the generated query].

The datatype refers to how the data exists in the database table itself. The docs state this is most useful to “increase query performance”. The specifics of how aren’t detailed, but I imagine it means less unnecessary casting if the datatype on your db table matches the required input for whatever function you’re using (date_trunc, as an example). As an example, if your db column is already a timestamp format, when using a time dimension_group it won’t need to cast the column to timestamp before using subsequent date format functions.

date_trunc(month,cast(x as timestamp))

would just be generated as

date_trunc(month,x)

That is my current understanding of its importance.

[rohit_shoppertr]

Type is something output form and datatype is something tells about input value form. Type is a mandatory  thing, we should define what kind of data will come out.

datatype is good to define, especially when there is a date or timestamp.

I think we should always try to define datatype and type, isn’t ?