MQL Date Time conversion from UTC to EST

[Chavya Khanna]

Hello All,

Was hoping if anyone of you could assist - basically i am experiencing an issue where DateTime format in my Payload is UTC "2022-03-02T20:48:29.847676541Z".

Now the intention is to convert this UTC time format to "DD-MM-YYYY hh:mm:ss:ssss" format. 

In Metric explorer I tried using query 

***metric.Timestamp is in UTC Format "2022-03-02T20:48:29.847676541Z"***

fetch cloud_function::logging.googleapis.com/user/metric_name | group_by[metric.IDmetric.Timestamp, string_to_timestamp("metric.Timestamp",'%m/%d/%Y %H:%M:%S:%f')]

But I am getting an error "Expression for time series identifier column 'string_lit_string_to_timestamp' had type 'Date' which is not a valid time series identifier column type"

Was hoping to get some form of assistance so I can convert UTC Date into a regular date time format.

Regards,

[ygrin...@google.com]

Hi, Chavya - does this help?

The solution is to leave the timestamp as a string, which the user was already including. It's not clear why they were trying to include both the string-typed timestamp and the date-typed timestamp in the group_by; you only need one. 

fetch cloud_function::logging.googleapis.com/user/Time_diff_Prescribe_IT | group_by[metric.UUID,metric.Timestamp]

[Chavya Khanna]

Thanks for your response Yuri,

Sorry if my ask is confusing but If you'd see the result still shows up in UTC Format "2022-03-04T20:21:19.655804289Z" - Where the expectation is to have this format converted into "DD-MM-YYYY hh:mm:ss:ssss" - That's reason the approach was to convert it from string to timestamp to be able to achieve "DD-MM-YYYY hh:mm:ss:ssss" format instead of existing format that has "T & Z" in it.

Hopefully this will help with clarity.

[Igor Peshansky]

Hi, Chavya,

There are a couple of issues with your original query.

First, it uses the string_to_timestamp function, which returns type Date. The error you saw simply stated that the Date type cannot be used as a time series label, which means you'd have to convert that date back to a string (using timestamp_to_string). You'd get the same exact error if you'd used any other Date-typed value (e.g., end()). Note that timestamp_to_string also allows you to specify a timezone.

Second, the time_format argument to string_to_timestamp and timestamp_to_string does not understand the '%f' format specifier -- you have to use a modifier on the '%S' format that specifies the number of digits you want to parse/print, e.g., '%E6S'. It also has to exactly match the (what looks like RFC3339) format of the date.

Finally, the auto-generated name for the computed column is pretty unwieldy. Luckily, you can actually name it in MQL.

So you would end up with something like this:

fetch cloud_function::logging.googleapis.com/user/Time_diff_Prescribe_IT | group_by[metric.UUID, metric.Timestamp, ts: metric.Timestamp.string_to_timestamp('%Y-%m-%dT%H:%M:%E6SZ').timestamp_to_string('%d-%m-%Y %H:%M:%E4S', 'EST')]

Hope this helps,

        Igor

--
© 2021 Google Inc. 1600 Amphitheatre Parkway, Mountain View, CA 94043
 
Email preferences: You received this email because you signed up for the Google Stackdriver Discussion Google Group (google-stackdr...@googlegroups.com) to participate in discussions with other members of the GoogleStackdriver community.
---
You received this message because you are subscribed to the Google Groups "Google Stackdriver Discussion Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-stackdriver-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/2c458f57-07cc-43fd-b6e4-d229eb2dd436n%40googlegroups.com.

[Chavya Khanna]

Thanks Igor,

So I tried your suggestion and it does make sense - However, the query returned with an error - Line 1: Unexpected '(' after a column name with '.'.

Realizing it's a column i've made a small change 

fetch cloud_function::logging.googleapis.com/user/Time_diff_Prescribe_IT | group_by[metric.UUID, metric.Timestamp, string_to_timestamp("metric.Timestamp",'%Y-%m-%dT%H:%M:%E6SZ').timestamp_to_string('%d-%m-%Y %H:%M:%E4S', 'EST')],  

However, this still returned with empty column, is this something to do with the Modifier used E6SZ?

Any suggestions on this one?

Error Returned:

Empty Column:

[Igor Peshansky]

Sorry about the typo — I didn't have access to the logs-based metrics in your project and tested with end() instead, where the "." syntax worked.

I suspect your query tells the API to parse the literal string "metric.Timestamp" as the timestamp, which fails. Try removing the quotes around metric.Timestamp.

Note that the format specifier is "%E6S" — the "Z" at the end is a literal "Z" (just like the "T" between the date and the time). You could also use "%ET" instead of "T" and "%Ez" instead of "Z" (the latter would parse any RFC3339 timezone, e.g., "-05:00").

Hope this helps,

        Igor

To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/c10d1afb-045f-4790-b2b3-a738b2b4d35fn%40googlegroups.com.

[Chavya Khanna]

Perfect thanks Igore, this works like a charm!

on the similar topic does MQL supports calculating difference of two datetime fields? 

[Chavya Khanna]

Hi Igor.

So i've noticed when we select EST - it shows 1 hour behind - Do have Daylight savings concept implemented at the time of selecting EST Timezone or any other for that matter where Daylight savings concept exist?

Regards,

Chavya Khanna

[Chavya Khanna]

Sorry but curious if anyone had a chance to look at why 

fetch cloud_function::logging.googleapis.com/user/Time_diff_Prescribe_IT | group_by[metric.UUID, metric.Timestamp, string_to_timestamp(metric.Timestamp,'%Y-%m-%dT%H:%M:%E6SZ').timestamp_to_string('%d-%m-%Y %H:%M:%E4S', 'EST')]

EST shows 1 hour behind.

Regards,

Chavya Khanna

[Igor Peshansky]

Glad to hear it helped. Yes, MQL does support subtraction of Date fields: https://cloud.google.com/monitoring/mql/reference#sub-func.

        Igor

To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/4a9f5cb1-3487-4428-9d4c-770e9f367859n%40googlegroups.com.

[Igor Peshansky]

As I recall, "EST" always means "with daylight savings off", while "EDT" means "with daylight savings on" (https://en.wikipedia.org/wiki/Eastern_Time_Zone). I'm not aware of a way to specify the timezone as "with whatever the current status of daylight savings is".

        Igor

To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/77eb8ba4-2d1c-446d-a163-97eac7ec1db8n%40googlegroups.com.

[Chavya Khanna]

Thanks again Igor! :)