Type: String Being Recognized as Date

(Simon Holstein) #1

I am attempting to use the to_date function so I can use the diff_seconds function on this String, but the to_date function believe the String is already of the type Date. Please let me know what I am doing wrong! Thanks.

(Krishna Potluri) #2

Hello Simon,

I think downtime___start is already declared as a date. Can you please check its declaration?

(will.adams) #3

Without knowing how this field is defined in the lookml, it’s hard to say for sure, but it looks like ${downtime_start} is a date field, so you should be able to use it in a diff_seconds() funtion by using one of the timeframes on that field like this:

diff_seconds(${downtime_start_time}, ${some_other_date_time})

Note the above assumes that both downtime_start and some_other_date are both dimension groups in the lookml. Hope this helps :slight_smile:

(Simon Holstein) #4

Thank you for the quick replies. If it is already a date, how come it says Type: String in the right side of the picture?

(Krishna Potluri) #5

I believe it might be argument type suggestion for the particular function (or might not be), not very sure on it.
Can you please check if it still shows Type: String even without downtime_start as an argument?
And apart from that, the error message itself is pretty clear.

(Simon Holstein) #6

It does not. Here it is with a date provided.

(Krishna Potluri) #7

That’s interesting… :thinking:
Can you please share definition of downtime_start?

(Simon Holstein) #8

It is a table calculation, which uses an IF statement to return the timestamp I shared in my last reply. Please see below.

if(${api_dataset.Count_500_Errors} != 0 AND offset(${api_dataset.Count_500_Errors} = 0, 1), ${api_dataset.timestamp_time},null)

(will.adams) #9

Right, so the issue here is that in your first example you had a date group, without a timeframe specified, so it read it as a string, but Looker knows it’s a date so it was preventing you from casting to date again (which would be redundant). In this second example, you’re referring to a specific timeframe within the dimension group, which Looker can also tell is a date, and is preventing casting again.

You should be able to use both ${downtime_start_time} and ${api_dataset.timestamp_time} in a diff_seconds() function without issue.

(Simon Holstein) #10

Hmm, ok maybe there is an issue elsewhere in the calculation. Thanks for the feedback on this.