How to convert string to timestamp in looker

Looker Q&A Looker
1

Hi team,

We have date string as 20210131120101(YYYYMMDDhhmmss) in table. We want to interpret this string into timestamp so looker can treat it as time stamp.

Then we want to extract date parts from 20210131120101:

2021: year

01: month

31: day

12:hour

01: minute

01:second

I have tried parse_timestamp function because my source database is bigquery but this is the error I am getting:

Thanks in advance

Thanks,

Rohit

2

I canā€™t believe thereā€™s no better way to do it in BigQuery O_o but this is the only thing I came up with:

SAFE.PARSE_DATETIME('%F %T', 
    CONCAT(
        SUBSTR('20210131120101',1, 4),
        '-',
        SUBSTR('20210131120101', 5, 2),
        '-',
        SUBSTR('20210131120101', 7, 2),
        ' ',
        SUBSTR('20210131120101', 9, 2),
        ':',
        SUBSTR('20210131120101', 11, 2),
        ':',
        SUBSTR('20210131120101', 13, 2)
    )
)

I would suggest wrapping it in a function

3

Dawid,

This is the data in my field:

I tried that substr function

but this small expression is failing.

Can you help me to fix this error:

Thanks,

Rohit

4

This function parsed the example you provided:

parse_timestamp('%Y%m%d%H%M%S',cast(20210131120101 as string))

To apply it to your column, you should use this and replace ā€œyour_columnā€ with the column name that contains your timestamp:

parse_timestamp('%Y%m%d%H%M%S',cast(your_column as string))

The error youā€™re getting on that message refers to the second argument of the function (the column containing the timestamp) not being of string datatype, but float datatype. You should cast your float to a string first, just like in the example.

I hope this helps.

Warmly,

5

I tried but this is

the new error:

6

This looks like your data isnā€™t always the same, meaning this error shows a value without the seconds, so do you have them all in the same format? if you have multiple formats you may need to use CASE with SAFE.PARSE_TIMESTAMP IS NULL

7

I have used:

This is the result:

Is this conversion fine?

I am surprised to see small letter m and d for month and day, capital M for minute. it should be YYYYMMDDhhmmss.

8

Dawid,

One last question on this thread and I am not sure it is relevant or not

In Qlik Sense, date or timestamp stores in numeric form in backend as 4993.32344 if timestamp and 4993 as date and we convert this number to any date as required format.

In Looker, how does raw or backend format of date stored? for example if I converted the string to timestamp then how actually it is stored?

20210101120101(YYYYMMDDhhmm) or in some number?

9

https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

Hereā€™s the reference for the format elements (link above). You might have to adjust based on this, but as Dawid said, it looks like not all of the data is in the same format. Using the prefix ā€œsafe.ā€ before the parse_timestamp function should return nulls whenever the function fails to parse the string because it is in a different format.

safe.parse_timestamp('%Y%m%d%H%M%S',cast(your_column as string))

Anyway, it looks like you got it working!

Regarding your storing questionā€¦ If Iā€™m not mistaken, Looker does not store any values in their backend. They simply run queries against your database based on the LookML youā€™ve written (LookML generates SQL, it doesnā€™t actually store stuff) and return the results, so your database dialect will determine how things are stored and parsed. In this case, dates or timestamps are stored precisely as date or timestamp datatypes. BigQuery has a lot of great documentation, so you should be able to clear any doubts using that.

10

Thanks @jbendinger-1633 for such great information. Actually my two tables have common field a timestamp which has format: %Y%m%d%H%M.

I want to apply a join between those such tables. What is the best way here to apply the join? should I apply the join on raw value whose format is %Y%m%d%H%M%S or convert it to another best format where join never fails?

11

If both your tables are storing the information in the same datatype and format, Iā€™d recommend just joining on the raw field. The reason for it is that the query will take a bit longer to run if you apply the parsing, because it will have to parse every record in each table and then compare those two records and join, whereas without parsing it would just evaluate if two numbers are equal without adding the extra step of parsing (I think you mentioned youā€™re storing them in float64 datatype).

Anyway, not a critical difference. However, if your columns are NOT storing the values exactly the same, I would recommend casting them to the same standard and then joining on them. Iā€™d also say give both approaches a try and then compare results. But again, all this is very dependent on your data and the uniformity of it.

Just as a quick example, if you had value_table_1 = 20210101000000 (Jan 1st 2021 at 00:00:00) and value_table_2 = 20210101000000 (same as before), joining on those two would evaluate to true because both numbers are exactly the same, therefore producing a good join. However, if you had something like value_table_1 = 202111000000 (Jan 1st 2021 but dropping the 0 from the month and day) and then value_table_2 = 20210101000000 (same as previous example, where months and days do have zeroes), you might have to cast them both to the same timestamp format using the format elements to produce 2 timestamps that look the same, because value_table_1 (202111000000) is different than value_table_2 (20210101000000). I hope what Iā€™m saying makes sense!

Warmly,

1 Like
12

@jbendinger-1633 you are right most of the time.

My use case says, I could have two kinds of time stamp value of one instant or I can say one hour.

My DB stores data up to hourly level, for example:

202101011100(YYYYMMDDhhmm) this is 1st Jan 2021, 11 AM and 0minutes.

Another value could be 202101011130(YYYYMMDDhhmm) this is 1st Jan 2021, 11 AM and 30 minutes.

Earlier we have noticed that this 30th minute cause problem in joining.

I want to round my field to 1/24, or I can say hour as:

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

This will always round the timestamp at hour level and join always work for both tables.

I am struglling how to do it in Looker+BQ.

I know how to do it other tool like Qlik but I am new here, need your help.

Can you please tell me how to round the timestamp by 1/24. 1/24 is the step. When I say step, it means, read below example:



Round(2,6 )

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ā€¦
13

Hi,
I have got a similar requirement but the parse_date function is not giving me the desired output.

I have a field in the table which is of data type string and I need to convert to date. The values are as 202203,202204 and so on. (YYYYMM). I need to convert this value into Month/Mon - Year.

So the desired output is : March 2022, April 2022. Any suggestions, please?