topic Re: How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset? in Data Engineering

How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset?

jonathan-dufaul — Tue, 21 Feb 2023 19:33:14 GMT

I have data that looks like this:

2021-11-25T19:00:00.000-0500
2021-03-03T13:00:00.000-0500
2021-03-09T15:00:00.000-0500
2021-03-13T16:00:00.000-0500
2021-03-19T03:00:00.000-0400
2021-05-28T03:00:00.000-0400

which is accurate, except I'm pulling the data to tableau server, and tableau displays all datetimes as UTC regardless of the local user's timezone. This reporting is leaving the local users confused, since the timestamps on the report do not match their timestamps for the process being reported (e.g. a user in Atlanta knows they started their process at 7:00AM, but the time in the tableau report is 12:00PM).

I want to be able to store the datetimes as the local time in databricks to work around this behavior, and have an additional column that stores the offset (like -5 for EST)

I have searched so much and have no idea if I'm just asking the wrong question or approaching the problem wrong. my end goal is displaying the correct date for every local user in tableau server. whatever the best practice is to accomplish this.

Re: How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset?

Anonymous — Fri, 24 Feb 2023 12:24:24 GMT

You have a couple options. There is a sql function to change the time zone.

You can also set the time zone on the cluster

Re: How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset?

jonathan-dufaul — Mon, 06 Mar 2023 14:43:48 GMT

I am familiar with those but I believe they only work to change the timezone for the entire session/cluster. I have multiple timezones in the dataset from different sites throughout the USA. I want a column to be "local time" and then another column to be "what timezone is the local time in"

For example:

local datetime                           timezone
2021-11-25T19:00:00.000        -0500
2021-03-03T13:00:00.000     -0500
2021-03-09T15:00:00.000     -0500
2021-03-13T16:00:00.000       -0500
2021-03-19T03:00:00.000      -0400
2021-05-28T03:00:00.000     -0400

local dateti                               timezone
2021-11-25T19:00:00.000       eastern
2021-03-03T13:00:00.000     eastern
2021-03-09T15:00:00.000     eastern
2021-03-13T16:00:00.000       eastern
2021-03-19T03:00:00.000      atlantic
2021-05-28T03:00:00.000     atlantic

Re: How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset?

Anonymous — Sat, 22 Apr 2023 06:42:17 GMT

Hi @Jonathan Dufault

Hope everything is going great.

Just wanted to check in if you were able to resolve your issue. If yes, would you be happy to mark an answer as best so that other members can find the solution more quickly? If not, please tell us so we can help you.

Cheers!

Re: How can I store timestamps using the local (not offset) timestamp instead of storing as UTC-Offset?

jonathan-dufaul — Tue, 25 Apr 2023 19:54:00 GMT

The question is not resolved and my most recent reply shows exactly what I want the output to be for a set of inputs. I don't know what else I can do to make it clearer.