Almost time to change your clocks, but now is the time to change your data.

Daylight savings time is coming up and it causes havoc with most of us. It also causes issues with our data. Timezones, daylight savings time, and UNIX timestamps all combine to cause crazy calculations.

Machine data often is based on GMT (Greenwich Mean Time). This is handy because computers all have their clocks set to the same time. It's like in the USSR where all trains ran on Moscow time. The benefit is that it's easy to compare times, but for humans we get confused when it doesnt line up with our watches.

The first step we run into sometimes is that UNIX timestamps. These are integer values that represent the number of milliseconds since the UNIX Epoch Jan. 1 1970.

Daylight Saving Time (DST) is some weird thing we do twice a year because of something to do with farmers, energy saving, and traffic accidents. Nobody is really sure.

There are a couple of methods of calculating all of these, but here is goes.

To convert a timestamp you can do this:

DATEADD('second', [Unix timestamp], #1970-01-01#)  

Now you have a GMT date. To convert it to Eastern Time (EST) and account for DST you need to -5 or -4 hours depending on if it is in the DST range. To determine the offset you can use this calc:

//determine if date [Date (GMT)] is in normal time
if 
 (datepart('day',[Date (GMT)])-datepart('weekday',[Date (GMT)])<7 
  and 
 datepart('month',[Date (GMT)])=3)
or 
 (datepart('day',[Date (GMT)])-datepart('weekday',[Date (GMT)])>=0 
  and 
 datepart('month',[Date (GMT)])=11)

or datepart('month',[Date (GMT)])<3

or datepart('month',[Date (GMT)])>11

then [Timezone offset]  //if it's normal time add the timezone diff from GMT (-5 for EST -8 for PST)
else [Timezone offset]+1  //if it isn't normal time its DST so add the offset +1 (-4 for EST and -7 for PST)
end

I usually roll them up into two calcs one for the above TZ offset and then the below calc that converts the timestamp and adds the offset.

DATEADD(
 'hour',
 [TZ and DST offset],
 DATEADD('second',INT([Unix Timestamp]),#1970-01-01#)
)

Tableau also have KB articles and Forum Posts that cover this as well.

To see how it all shakes out we have a sample viz.

Also note that some of these adjustments would have to be changed a bit to accommodate places like Australia and Newfoundland that have half hour time zone variance.