Time Formats in Tableau Server Postgresql Data

It’s been a while since I’ve posted an article, but I tend to believe that no news is good news! I’ve been working hard at my placement and learning a mountain of stuff. I’ve been itching to write a new post to really ground the experience in my brain, and hopefully help other folks out who might be struggling with the same analytics issues.

Recently, I was working with the Tableau Server postgresql  data and hit a bit of a bump when I realised the times in the dataset weren’t in the timezone format I needed. Why is this important? Well, let’s say you’re trying to determine the times when your dashboards are viewed the most, or times when you have concurrent users on your server to determine how much of a strain you’re putting on your system. By default, the times included in the postgresql data are in UTC format. So in order to put the time in a format that is meaningful in your timezone, you will need to do some manipulation.

One thing that complicates the situation is daylight savings time. Although you could do a simple DATEADD formula and convert UTC time to your timezone, how can you account for times in the year when the time changes? What if you are looking at multiple regions with different daylight savings time rules?  In a comment posted in response to Mark Jackson’s article , Brandi Beals provides a conversion calculation that addresses this issue.

First, you will need to determine the daylight savings time rules for your own country. In Brandi’s comment, she outlines a calculation for time conversion based on the daylight savings time rules in the United States for CST. She ends up with a calculation that looks like this:

DATEADD(‘hour’,
IF
DATEPART(‘month’,TODAY())>=3      //Starts in March
AND DATEPART(‘week’,TODAY())>=10  //Starts 2nd Sunday ~10th week of the year
AND DATEPART(‘month’,TODAY())<11  //Ends in November
AND DATEPART(‘week’,TODAY())<45   //Ends 1st Sunday ~45th week of the year
THEN -5 //Daylight Savings Time
ELSE -6 //Standard Time
END
,[Started At])

Let’s break this down. In the first DATEPART line, we are looking for dates that start after March, when daylight savings time occurs in the States. In the second DATEPART line, we are looking for dates that start after and including the 10th week of the year. Why? Because not only does daylight savings time start in March in the US, but it also starts on the 2nd Sunday of the month, which is approximately the 10th week of the year.

Okay. Pause. How do we know the second Sunday of March is the 10th week of the year? Well, we could take out our calendars and count through the weeks until we get to the date we need. Or we could use something called the ISO week date. The ISO week date is a calendar system that will give us the week a date is located in, while still accounting for leap years. The calculation is a bit tricky, but if you’re curious to see how it’s done, Wikipedia has a great summary of instructions here. Otherwise, you can go to www.epochconverter.com/weeks for a handy table of dates and their corresponding week numbers.

Note that even with using this method, we can’t be 100% accurate because leap years will sometimes change the week number where a weekday lies. However, this will only vary by 1, so it should not significantly affect your data, but it’s something to be aware of.

If we go back to Brandi’s calculation, we can see that the last two DATEPART lines include dates before the first Sunday of November (the 45th week in the 11th month). We can then complete our IF THEN calculation by using a numerical conversion from UTC to CST (-5), ELSE a numerical conversion for Standard Time (-6).

In the UK, the rules for daylight savings time are slightly different, but the overall calculation remains the same. Based on a daylight savings time (i.e. British Summer Time) that starts on the last Sunday of March, ends on the last Sunday of October, and a UTC to GMT offset of +1, we end up with a calculation for the UK that looks like this:

DATEADD(‘hour’,
IF DATEPART(‘month’,TODAY())>=3
AND DATEPART(‘week’,TODAY())>=12
AND DATEPART(‘month’,TODAY())<10
AND DATEPART(‘week’,TODAY())<43
THEN 1
ELSE 0
END
,[Started At])

Although it’s a bit of a pain to get the conversion calculation figured out to account for daylight savings time, it will give you the most accurate time information when monitoring information output from Tableau Server’s postgresql data. In addition, you can use these calculations when trying to look at time information from multiple time zones, while accounting for different daylight savings rules in each region.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: