I have some dates stored as integers using the Microsoft date format (days since December 30, 1899: https://msdn.microsoft.com/en-us/library/82ab7w69.aspx )
Is there a clean way to convert this to a date within Looker in order to calculate weekly totals?
Typical values are 41094, 41103, etc.
Hi Jason,
One solution that might work is using a date add function. Here it is for Redshift
- dimension: converted_date
sql: dateadd(date,${microsoft_date},'1899-12-30')
```
Since you are looking for weekly totals you should be okay, but for more granular detail (such as hourly) you may need to verify daylight savings time/timezones.
More information on date add functions:
* Redshift: http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html
* MySQL: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
* Postgres: https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQLThank you. I’m using Greenplum, so the PostgreSQL solution will probably work. 🙂
And, dates already normalized to day, so no worries there.
Here’s my working Greenplum code:
- dimension: converted_date
sql: TIMESTAMP '1899-12-30' + (${order_date} * INTERVAL '1 days')
TY for the assistance.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.