cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to convert string to datetime with correct timezone?

najmead
Contributor

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?

6 REPLIES 6

daniel_sahal
Esteemed Contributor

@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

image

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?

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)

Manoj12421
Valued Contributor II

Use to_utc_timestamp(expr,timezone)​

SRK
Contributor III

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

image

Rajeev_Basu
Contributor III

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!