How to add hours to my timeframe field?

Looker Q&A Looker
,
1

I have a dimension group like this–

dimension_group: timestamp{
type: time
description: “day-month-year time of leave”
timeframes: [
raw,
day_of_week,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.“timestamp” ;;
}

I am trying to create a new field out of this where I get +8 hours with this timestamp field, something like this–

dimension_group: timestamp+8hours{
type: time
description: “day-month-year time of leave”
timeframes: [
raw+8hours,
day_of_week+8hours,
time+8hours,
date+8hours,
week+8hours,
month+8hours,
quarter+8hours,
year+8hours
]
sql: ${TABLE}.“timestamp” ;;
}

basically, every time frame(date, time etc) will be increased by 8 hours!

example: 2021/03/2120:00:00 + 8 hours = 2021/03/22 04:00:00

2

If I tried to do it in BigQuery, I would do the following:

dimension_group: timestamp_8_hours {

  sql: DATE_ADD(${timestamp}, INTERVAL 8 HOUR) ;;

  timeframes: [month, quarter, week, year]

}

You have to add it in sql not the timeframes. The timeframes is an array of keywords that are not editable in anyway

1 Like
3

thanks Dawid! also, do you know if this is the same method to convert my db time which is in UTCto GMT?

4

What is your DB? SQL server, big query, mysql?

5

Its Postgresql, Dawid!

6

I think in PostgreSQL you do ${timestamp} + INTERVAL ‘8 hours’.

When it comes to timezones in PostgreSQL it depends whether your data is of type TIMESTAMP or TIMESTAMP WITH TIMEZONE

7

In my postgresql, the timestamp is without the timezone. However, I know that it is in UTC. So will this work?

8

Although, I tried what you suggest here but it still doesn’t work! @Dawid

9

I don’t know what will definitely work as I don’t have access to PostgreSQL. You need to check SQL implementation for PostgreSQL to find the right function. Why does it matter that database is in UTC? You only said that you want to add 8 hours to your timestamp. Are you trying to convert it into a different time zone?

10

I think my database is in UTC but I need to show it in GMT for quality purposes in Looker. But thanks Dawid, it really helps!

11

I think you can use this kind of construct. In this topic they’re talking about converting to UTC but you just need to apply the reverse:

https://stackoverflow.com/questions/18188151/how-to-convert-local-time-to-utc