0

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.

AdamH
  • 25
  • 6
  • You could use GetUTCDate() to calculate the current offset instead of hard-coding it. `SELECT DATEDIFF(hour,GetUTCDate(),GETDATE())` --Assuming SQL Server – Hart CO Jul 14 '16 at 15:35
  • 1
    Are you using SQL Server? If so, please add this tag to your question. – Tim Biegeleisen Jul 14 '16 at 15:36
  • You might have to [create a custom function](http://stackoverflow.com/questions/19732896/how-to-create-daylight-savings-time-start-and-end-function-in-sql-server) to handle daylight savings time. – Tim Biegeleisen Jul 14 '16 at 15:37
  • Sorry for the lack of information. I'm not very good in SQL. The service connection appears to show I'm using SQL Server. I will add this tag to the question. Also, I will try using some of your suggestions and report back what I find. I'll probably have to play with it a little before I understand how it works. – AdamH Jul 14 '16 at 17:46

2 Answers2

0

I'm assuming you're using SQL Server. If you only ever need the current offset, you can use GETUTCDATE() to calculate it:

SELECT DATEDIFF(hour,GetUTCDate(),GETDATE())

If you need to calculate the offset for other dates you'll likely want to use a CLR function: https://dba.stackexchange.com/questions/28187/how-can-i-get-the-correct-offset-between-utc-and-local-times-for-a-date-that-is

Which links to: https://blogs.msdn.microsoft.com/sqlserverfaq/2011/07/29/how-to-convert-utc-time-to-local-time-in-sql/

Community
  • 1
  • 1
Hart CO
  • 34,064
  • 6
  • 48
  • 63
0

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.

Community
  • 1
  • 1
Terry Carmen
  • 3,720
  • 1
  • 16
  • 32
  • I was able to get the GetUTCDate function working to adjust for DST. I can't figure out how to get the CONVERT function to run. It keeps giving me the error message "conversion failed when converting date and/or time" when I run CONVERT(date, 'Time of Event') = '2016-07-11' Any ideas? Also, any idea what I would use to automatically have it use yesterday's date instead of hard-coding it? – AdamH Jul 14 '16 at 18:49
  • Can you paste the actual message? – Terry Carmen Jul 14 '16 at 23:09
  • I finally got it working. I think it didn't like me calling the column I had named "Time of Event" as the input. I had to use the whole time conversion from the SELECT statement in the WHERE statement. Below is what the WHERE statement and SELECT statement ended up being. SELECT DATEADD(hour,DATEDIFF(hour,GetUTCDate(),GETDATE()),DATEADD(SECOND,aa.fldTimeOfEvent,'1970-01-01 00:00:00')) DATEADD(dd,0,DATEDIFF(dd,0,DATEADD(hour,DATEDIFF(hour,GetUTCDate(),GETDATE()),DATEADD(SECOND,aa.fldTimeOfEvent,'1970-01-01 00:00:00')))) = CONVERT(date,GETDATE()) – AdamH Jul 15 '16 at 20:25