topic Re: How to convert string to datetime with correct timezone? in Data Engineering

How to convert string to datetime with correct timezone?

najmead — Wed, 25 Jan 2023 04:33:32 GMT

I have a field stored as a string in the format "12/30/2022 10:30:00 AM"

If I use the function TO_DATE, I only get the date part... I want the full date and time.
If I use the function TO_TIMESTAMP, I get the date and time, but it's assumed to be UTC, which isn't correct (it's actually the local timezone where the data was originally hosted)
I can potentially use MAKE_TIMESTAMP, but I have to do a lot of string manipulation to get all the components

What is the easiest way to convert the string into a timestamp, with the timezone of my choosing?

Re: How to convert string to datetime with correct timezone?

daniel_sahal — Wed, 25 Jan 2023 06:42:37 GMT

@Nicholas Mead

You can use TO_TIMESTAMP() function with format parameter.

Ex.

TO_TIMESTAMP("12/30/2022 10:30:00 AM", "MM/dd/yyyy HH:mm:ss a") AS tsDate

Re: How to convert string to datetime with correct timezone?

SRK — Wed, 25 Jan 2023 12:07:29 GMT

You can use this to get the time zone of your choosing:

Re: How to convert string to datetime with correct timezone?

najmead — Mon, 30 Jan 2023 07:06:18 GMT

I don't seem to be getting the same result... note no timezone info in my results;

Is there a config setting I'm missing or something?

Re: How to convert string to datetime with correct timezone?

najmead — Tue, 31 Jan 2023 00:57:29 GMT

Just to answer my own question, in case anyone else googles for this...

FirstIy, don't know why timezone isn't displaying in my query results using a SQL Warehouse, but it works ok using a compute cluster, so I'm guessing it is a display issue rather than a data issue.

Secondly, from what I can tell, using the TO_TIMESTAMP function will apply the timezone for your session (which might not be the timezone you want). I didn't realise this could be solved pretty easily by simply pasting your timezone to the end of the date string, like this;

TO_TIMESTAMP(MyDateTimeString || ' Timezone', 'M/d/y h:m:s a z)

For example;

TO_TIMESTAMP(MyDateTimeString || ' America/Los_Angeles', 'M/d/y h:m:s a z)

Re: How to convert string to datetime with correct timezone?

Rajeev_Basu — Fri, 03 Feb 2023 11:48:45 GMT

use from_utc_timestamp(to_timestam("<string>", <format>),<timezone>)

Re: How to convert string to datetime with correct timezone?

Manoj12421 — Sat, 04 Feb 2023 14:30:06 GMT

Use to_utc_timestamp(expr,timezone)

Re: How to convert string to datetime with correct timezone?

David_Billa — Fri, 20 Dec 2024 18:18:47 GMT

TO_DATE("12/30/2022 10:30:00 AM", "MM/dd/yyyy HH:mm:ss a") AS tsDate

Is this correct if we need only date?