I am extracting timestamps in Unix Epoch Time format and simply converting them with a DATEADD formula however it does not take into account DST...how would one achieve that when using an Epoch time from the database extract?
Calculated FIeld:
DATEADD('second',[BEGINDATETIME],#January 1,1970# )
The existing calculated field will successfully convert the epoch time (1,398,738,922) to a human readable long date format (i.e. mm/dd/yyyy hh:mm:ss) however it does not take into account the daylight savings time offset. How could one do this in a calculated field in Tableau or is this something that needs to be handle "pre-extract"?
-Derek
Shawn's calculation should definitely solve your need for this year. If you need something dynamic for other years, the following should accomplish this. It dynamically finds the start of Daylight Savings Time for any year for which you have data, regardless of whether or not you have data on that specific date/month. It does the same thing for the end of DST, and makes sure that your date falls within this. It also accounts for the 2AM hour for either end.
I put it all in 1 calc as it's a "black box" that just works and cuts down for "clutter" for your end user. Not as fun to develop, but should allow you to just paste into any workbook and integrate into your view. All you have to do is substitute your time field for Order Date (I'm assuming Epoch Time). If you need a workbook, let me know.
IF [Order Date] >= DATEADD('hour', 2, (IF DATEPART('weekday', DATEADD('month', 2, DATETRUNC('year', [Order Date]))) = 1
THEN DATEADD('month', 2, DATETRUNC('year', [Order Date])) + 7
ELSE DATETRUNC('week', DATEADD('month', 2, DATETRUNC('year', [Order Date])) + 13)
END))
AND
[Order Date] <= DATEADD('hour', 2, (IF DATEPART('weekday', DATEADD('month', 10, DATETRUNC('year', [Order Date]))) = 1
THEN DATEADD('month', 10, DATETRUNC('year', [Order Date])) + 7
ELSE DATETRUNC('week', DATEADD('month', 10, DATETRUNC('year', [Order Date])) + 6)
END))
THEN DATEADD('hour', 1, [Order Date])
ELSE [Order Date]
END
It should also be noted that I would encourage handling this in your datasource, but if this is not an option, the above works well (albeit with a slight performance hit due to calculation time).
