I have two questions regarding querying data in SQL that uses Epoch time stamps. I'm able to convert the date in my SELECT statement using this text:
DATEADD(HOUR, -4, DATEADD(SECOND, aa.fldTimeOfEvent, '1970-01-01 00:00:00'))
I had to use the "hour,-4" to convert it to Eastern daylight savings time. I'm curious if I will need to adjust this to -5 in November when daylight savings time ends? Is there a way to formulate the SELECT statement so it automatically adjusts for DST? This will be part of an automated report and I'm afraid we may miss changing this value.
Another question is how to use the WHERE statement to get data for a certain day. For instance, if I wanted the WHERE statement to grab all entries that occurred any time today (7/14/16), how would I do that? I've tried this statement:
WHERE DATEADD(HOUR, -4, DATEADD(SECOND, aa.fldTimeOfEvent, '1970-01-01 00:00:00')) = '2016-07-14'
It appears to only grab the entries that are exactly equal to midnight of that day (I think). I want all entries that occurred for any time the day.
Thanks in advance for your help.
The easy part first:
If you need to compare the date only, convert your datetimes to dates before comparing with: CONVERT(date, yourdatefield)
The second part has been discussed and solved here for UTC time: Convert Datetime column from UTC to local time in select statement. You would still need to add in the offset for Epochtime.