Category Archives: Conversion

Tableau UTC Conversion

In one of my earlier posts, I wrote about how to manually convert UTC time to Eastern time, adjusting for the Standard vs Daylight Savings. I’ve found this formula useful for some of SQL work but recently needed to incorporate it into Tableau.

So I decided to share the following calculated field. Simply cut and paste the formula below into your favorite text editor and Replace All the [date_field] with your date column name.

if date([date_field]) > #13-03-2011# and date([date_field]) < #06-11-2011# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2012# and date([date_field]) < #04-11-2012# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2013# and date([date_field]) < #03-11-2013# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2014# and date([date_field]) < #02-11-2014# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2015# and date([date_field]) < #01-11-2015# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2016# and date([date_field]) < #06-11-2016# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2017# and date([date_field]) < #05-11-2017# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2018# and date([date_field]) < #04-11-2018# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2019# and date([date_field]) < #03-11-2019# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2020# and date([date_field]) < #01-11-2020# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #14-03-2021# and date([date_field]) < #07-11-2021# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2022# and date([date_field]) < #06-11-2022# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2023# and date([date_field]) < #05-11-2023# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2024# and date([date_field]) < #03-11-2024# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2025# and date([date_field]) < #02-11-2025# then DATEADD(‘hour’,-4,[date_field])
else DATEADD(‘hour’,-5,[date_field]) end

Please note, this converts UTC to US Eastern time zone. So if you’re in another time zone, be sure to adjust the DATEADD¬†values. I hope this helps.

Share Button

Manual UTC Conversion

During a project last year I was creating a FACT table from one of many databases in my organization used to track program information. Using SQL Server, one of the fields that I wanted to pull was Air Date/Time of a program. When I first created my fact table, I noticed that the Air Date/Time field was in UTC. I suspect this database was created with a time OFFSET field but I didn’t have schema documentation for it.

I decided create my own conversion calculation to East Coast time. I’ve been working with SQL for a little time now but I’m no expert. So the challenge I faced was adjusting for Standard versus Day Light Savings because UTC does not change. I looked up the dates when we adjust our clocks from 2011 – 2025. The rest was the following SQL CASE statement:

CASE
WHEN [Table].[Date Field] BETWEEN ‘2011-03-13 07:00:00:000’ AND ‘2011-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2012-03-11 07:00:00:000’ AND ‘2012-11-04 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2013-03-10 07:00:00:000’ AND ‘2013-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2014-03-09 07:00:00:000’ AND ‘2014-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2015-03-08 07:00:00:000’ AND ‘2015-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2016-03-13 07:00:00:000’ AND ‘2016-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2017-03-12 07:00:00:000’ AND ‘2017-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2018-03-11 07:00:00:000’ AND ‘2018-11-04 06:00:00:000 THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2019-03-10 07:00:00:000’ AND ‘2019-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2020-03-08 07:00:00:000’ AND ‘2020-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2021-03-14 07:00:00:000’ AND ‘2021-11-07 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2022-03-13 07:00:00:000’ AND ‘2022-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2023-03-12 07:00:00:000’ AND ‘2023-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2024-03-10 07:00:00:000’ AND ‘2024-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2025-03-09 07:00:00:000’ AND ‘2025-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
ELSE DATEADD(hour,-5,[Table].[Date Field]) END AS [Date Name]

I first created a parameter in Tableau to let the user decide to view the data between standard and daylight savings. But I wanted the users to avoid any confusion between the viz and any other area where one can see program air date/time. Secondly, for Tableau best data practices, do heavy data lifting calculations before connection to Tableau. Software like SQL Server are built for this type of data manipulation. Especially when working with millions of records, long winded calculations can slow Tableau performance.

Thanks to my manager for encouraging this post. I hope it saves anyone time who needs to convert UTC date fields. 

UPDATE: To perform the same in Tableau click here.

Share Button