Hello Guys,
Is there a way I can get the timezone offsets , I mean the Integer value . Bascially I want to perform some custom operations by comparing dates from a timezone and UTC.
I am referring to time offsets mentioned here
Thanks
Since JSON doesn’t have a date type, timestamps are usually strings - the offset is a literal part of the string. If you’re storing milliseconds instead, these are expected to be UTC millisecond values (since it is just an integer and carries no time zone meta data) and the formatting functions (typically) will take a time zone (defaults to the server time zone) and perform the necessary conversion for you. (That is, you’ll typically ingest a string converting to a UTC millisecond value - the conversion takes place at ingest time and all integer values in the database are UTC.)
(Sometimes components are separate string fields and are then usually concatenated to form a coherent time stamp that can be processed by the functions.)
Remember with daylight savings the differences between time zones end up being specific time/date dependent, so absolute offsets aren’t always applicable.
(If all you’re after is a list of time zone names and values, I’d have to direct you to web resources such as timeanddate.com .)
I may be able to suggest something else if you could elaborate on the sort of calculation you might want to perform and how your timestamps are stored (strings or integer milliseconds) ?
→ If you have string timestamps (with zone information) then I’d suggest STR_TO_MILLIS and operate on the integer results.
→ STR_TO_UTC would leave you with UTC string timestamps you could then operate on/with
- ISO format timestamp strings can be compared / sorted etc. directly.
HTH.