topic Re: Timezone in silver tables in Data Engineering

Timezone in silver tables

Freshman — Mon, 03 Feb 2025 00:28:53 GMT

Hello,

What is the best practice in Databricks for storing DateTime data in silver layer tables, considering the source data is in AEST and we store it in UTC by default?

Thanks

Re: Timezone in silver tables

filipniziol — Mon, 03 Feb 2025 08:37:21 GMT

Hi @Freshman ,

By converting you datetime columns from AEST to UTC and storing as UTC you are following best practices.
This way you standardize the the time zone and avoid confusion, in case you will have more data from multiple time zones.

When you need to display the data in a local time zone (like AEST for end users), perform the conversion from UTC to the desired time zone in your reporting layer.

Additionally, as per documentation, Databricks does not have any datetime format that would contain the information about the time zone. You need to use TIMESTAMP, so converting to UTC before storing in silver is you safest bet.

Re: Timezone in silver tables

robert154 — Mon, 03 Feb 2025 08:39:17 GMT

@Freshman wrote:
Hello,
What is the best practice in Databricks for storing DateTime data in silver layer tables, considering the source data is in AEST and we store it in UTC by default?
Thanks

The best practice for storing DateTime data in the Silver layer of a Databricks data lake is to store it in UTC and include a separate column for the original timezone. This ensures data consistency and allows for accurate time zone conversions during analysis. Utilize Databricks SQL functions like from_utc_timestamp() and to_utc_timestamp() for efficient time zone handling.

Re: Timezone in silver tables

Freshman — Mon, 03 Feb 2025 22:45:10 GMT

Thanks @filipniziol for you advice, appreciate it, that gives confidence in our solution design. yes we are do the conversion in the Reporting/Gold layer.

Re: Timezone in silver tables

Freshman — Mon, 03 Feb 2025 22:45:30 GMT

Thanks @robert154 for you advice, appreciate it, that gives confidence in our solution design. yes we are do the conversion in the Reporting/Gold layer.