[SQL] Converting Unix Date Time

[kamara veldereth]

Fuck Unix date time...

I know we have some smattering of SQL gurus here.  Basically, I need to be able to convert UnixDate to Julian date/time when DST in play (accommodate both situations to where if current date not DST and date to convert is as well as if current date is DST and date to convert is not).  Below is what I have so far... I'm trying to avoid plugging in the date/time that DST takes effect into a table and having func read from there... link here for scheduled DST changes

Somehow using logic involving datepart(d,) and datepart(dw,) to get day of month and day of week (sun=1,mon=2,tue=3,etc) is probably the key, but solution eludes me so far.

Below func, so far, basically just treats apr-oct as DST months and disregards cutover in mar and nov until I figure out the logic to handle exact cutoff at 2am 2nd sun in mar and 2am 1st sun in nov.  Building a table storing it would be the easiest way out, but trying to avoid creating unnecessary tables.

The problem seems simple at first until trying to figure out how to programmatically find 2nd sun in mar and 1st sun in nov regardless of yr.  Blesses for a week to person who finds solution.

CREATE FUNCTION [dbo].[ConvertDate]
(
      @TimeValue int
)
RETURNS datetime
AS
BEGIN       
   DECLARE @DateTime datetime
   DECLARE @GMTOffsetInSeconds int

   DECLARE @CurrDate datetime
   DECLARE @isDateDST bit
   DECLARE @isCurrDateDST bit

   SET @GMTOffsetInSeconds = DateDiff(ss, GetUTCDate(), GetDate())
   SET @DateTime = DATEADD(s,@TimeValue+@GMTOffsetInSeconds,'01/01/1970')
   SET @CurrDate = GETDATE()

   IF DATEPART(m,@CurrDate) BETWEEN 4 AND 10
      SET @isCurrDateDST = 1
   ELSE
      SET @isCurrDateDST = 0

   IF DATEPART(m,@DateTime) BETWEEN 4 AND 10
      SET @isDateDST = 1
   ELSE
      SET @isDateDST = 0            

   IF @isCurrDateDST = 1 AND @isDateDST = 0
      SET @DateTime = DATEADD(ss,-3600,@DateTime)

   IF @isCurrDateDST = 0 AND @isDateDST = 1
      SET @DateTime = DATEADD(ss,3600,@DateTime)
     
   RETURN @DateTime
END

[kamara veldereth]

During DST, dates in eastern time zone effectively become as though they're GMT -4 so as a test, after creating func and changing logic, the following should return 3/14/2010 2:30am:

SELECT dbo.ConvertDate (DATEDIFF(ss,'01/01/1970','03/14/2010 2:30')+14400)

[kamara veldereth]

Posted in sqlservercentral.com and sqlteam.com a few hrs ago, but no bites yet 

[Quintain]

Oracle or SQL server?

or is this some sort of homework problem?

1) Instead of building new tables, use constant variables to define the Epoch time of the specific dates that change, then build your logic around testing against those specific values.

2) If the OS is windows, use UTC.

[kamara veldereth]

Thanks for response...

deving in SQL 2005 and XP, but will be deployed to environments using sql 2000, 05, and 08 and all flavors of windows

Working on building some BI reports that will use this func to convert Unix Dates to Julian so it will be referenced in many different queries.  Unix OS doesn't play a role in this at all, it's just that the application that dates are read from stores the dates as Unix datetime (believe because this application was ported from Unix years ago).

1)  Not sure what exactly you mean... I don't really want to hardcode specific start/end dates anywhere... I'd like for the func to accurately recognize 2nd Sunday in Mar and 1st Sunday in Nov regardless of year (current Daylight Saving Time schedule is valid until 2015, at which point, it's likely same DST rules will apply moving forward, but believe it's voted on)

2)  I am using UTC to get correct GMT offset DateDiff(ss, GetUTCDate(), GetDate()), but during DST, the application effectively stores dates as though they're minus another GMT offset (i.e. Eastern Standard Time would store as though it's GMT -4, CST would store as though it's GMT -5, etc).

[Quintain]

1) ok, so the @CurrDate is a string?  And based on that date, you want to add (subtract) either 0 or 1 to a set timezone difference?

[Sifter]

will take a look later tonight

[Quintain]

This might help you:

http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

[kamara veldereth]

1) ok, so the @CurrDate is a string?  And based on that date, you want to add (subtract) either 0 or 1 to a set timezone difference?

DECLARE @CurrDate datetime

The reason I need @CurrDate is because I need to compare that with the date that needs evaluated.  If @CurrDate is nonDST and @DateTime (date being evaluated) is DST, then @DateTime needs reduced by an hr to accommodate 'to nonDST from DST'.  Conversely, if @CurrDate is DST and @DateTime is nonDST, then @DateTime needs added by an hr to accommodate 'from nonDST to DST'.

Scenario:  I'm running a report in January that is evaluating "Unix datetimes" from all of prior year.  Given Jan is nonDST, dates falling between 2nd Sunday in Mar at 2am and 1st Sunday in Nov at 2am need adjusted backward by an hour to accommodate date evaluation during nonDST of DST times.

Conversely, if I'm running same report in June for all data in the last year (from June of prior year to May of current year), then the opposite needs to take place in order to show correct time for dates between 1st Sun in Nov at 2am and 2nd Sun in Mar at 2am.

EDIT ~ to/from DST/nonDST mixed up in explanation paragraph

[kamara veldereth]

This might help you:

http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

Looks intriguing and could quite possibly be what I need... will look at this further after I've been properly caffeinated for the day.  Thanks!

[Quintain]

1) ok, so the @CurrDate is a string?  And based on that date, you want to add (subtract) either 0 or 1 to a set timezone difference?

DECLARE @CurrDate datetime

The reason I need @CurrDate is because I need to compare that with the date that needs evaluated.  If @CurrDate is nonDST and @DateTime (date being evaluated) is DST, then @DateTime needs reduced by an hr to accommodate 'to nonDST from DST'.  Conversely, if @CurrDate is DST and @DateTime is nonDST, then @DateTime needs added by an hr to accommodate 'from nonDST to DST'.

Scenario:  I'm running a report in January that is evaluating "Unix datetimes" from all of prior year.  Given Jan is nonDST, dates falling between 2nd Sunday in Mar at 2am and 1st Sunday in Nov at 2am need adjusted backward by an hour to accommodate date evaluation during nonDST of DST times.

Conversely, if I'm running same report in June for all data in the last year (from June of prior year to May of current year), then the opposite needs to take place in order to show correct time for dates between 1st Sun in Nov at 2am and 2nd Sun in Mar at 2am.

EDIT ~ to/from DST/nonDST mixed up in explanation paragraph

The thing of it is, any unix date/time that was generated(via the OS) from last year will have already been adjusted for DST in the timestamp itself.  So, any timestamp saved will correctly reflect the date/time that it was generated and take into account DST (unless the timestamp was in GMT).

[kamara veldereth]

The Unix datetime (at least for this application) always stores as UTC.  During nonDST, 1pm EST today would store as 6pm given that EST is GMT -5.  During DST... let's say Mar 20... 1pm EDT would store as though it were 5pm given that it's Eastern time zone, but during DST (almost no different than if it were saving as though eastern time zone was in GMT -4 offset time zone).

[kamara veldereth]

By the way, using blurbs from that function, I have a script that will correctly translate dates to/from DST/nonDST so many thanks, sir

The beauty of that logic is that it doesn't use DATEPART(dw,), which can be a moving target based on @@DATEFIRST

[kamara veldereth]

FYI... this is the final product... I'm still looking at ways I can make the long, confusing shit a little less confusing and not quite as verbose, and still needs doc'd/commented but this works for the time-being so is what will probably make it to production:

ALTER FUNCTION [dbo].[ConvertDate]
(
      @TimeValue int
)
RETURNS datetime
AS
BEGIN       
   DECLARE @DateTime datetime
   DECLARE @GMTOffsetInSeconds int

   DECLARE @CurrDate datetime
   DECLARE @isDateDST bit
   DECLARE @isCurrDateDST bit
   DECLARE @DSTStart datetime
   DECLARE @DSTEnd datetime

   SET @GMTOffsetInSeconds = DateDiff(SECOND, GetUTCDate(), GetDate())
   SET @DateTime = DATEADD(SECOND,@TimeValue+@GMTOffsetInSeconds,'19700101')
   SET @CurrDate = GETDATE()

   SET @DSTStart = DATEADD(YEAR,DATEDIFF(YEAR,'19000301',@DateTime),'19000301')
   SET @DSTStart = DATEADD(DAY, 7 + (13 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, 2, @DSTStart), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 2, @DSTStart), '19000101')) + '19000101 1:00'

   SET @DSTEnd = DATEADD(YEAR,DATEDIFF(YEAR,'19001101',@DateTime),'19001101')
   SET @DSTEnd = DATEADD(DAY, (13 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, 1, @DSTEnd), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 1, @DSTEnd), '19000101')) + '19000101 1:00'

   IF @DateTime BETWEEN @DSTStart AND @DSTEnd
      SET @isDateDST = 1
   ELSE
      SET @isDateDST = 0   

   SET @DSTStart = DATEADD(YEAR,DATEDIFF(YEAR,'19000301',GETDATE()),'19000301')
   SET @DSTStart = DATEADD(DAY, 7 + (13 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, 2, @DSTStart), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 2, @DSTStart), '19000101')) + '19000101 1:00'
   
   SET @DSTEnd = DATEADD(YEAR,DATEDIFF(YEAR,'19001101',GETDATE()),'19001101')
   SET @DSTEnd = DATEADD(DAY, (13 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, 1, @DSTEnd), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, 1, @DSTEnd), '19000101')) + '19000101 1:00'

   IF @CurrDate BETWEEN @DSTStart AND @DSTEnd
      SET @isCurrDateDST = 1
   ELSE
      SET @isCurrDateDST = 0

   IF @isCurrDateDST = 1 AND @isDateDST = 0
      SET @DateTime = DATEADD(HOUR,-1,@DateTime)

   IF @isCurrDateDST = 0 AND @isDateDST = 1
      SET @DateTime = DATEADD(HOUR,1,@DateTime)
     
   RETURN @DateTime
END


Out of curiosity, what is your background, Quint?  You seem to know a lot about a lot... just curious as I'm impressed.

[Jackpanel]

Maybe overcomplicating this, but...

Where is the original UNIX date coming from? Are the times being gathered from multiple remote locations?  Have you taken into consideration that some regions (e.g. Arizona, Saskatchewan) don't use DST?

We've had to deal with those kind of questions in the past, and I believe our decision was "Fuck Saskatchewan"

[kamara veldereth]

The original UNIX date is coming from a database field from a different application (Gentran:Server for Windows, which isn't Unix, but the datetimes stored are "UNIX" format because I believe the technological ancestor of GSW was UNIX based)... the script will be deployed in multiple environments, but for a given environment it would only be processing times for that same time zone (script won't be deployed to PST and process times that are EST).

For the time-being, this will only be run in the continental US, and the only region that doesn't recognize DST in the US is portions of AZ and I'm taking a similar "fuck arizona" approach, although it would be possible to add another pass-in param to the func to accept a 0/1 bit for @isDSTinUse or whatever that would be set within a setup form of some sort, but I'm not too concerned with that yet.

This also won't accommodate timedates set prior to 2007, when the time zone change was in Apr and Oct instead of Mar and Nov, but accommodating pre-2007 datetimes are out of scope.

[Solayce]

You may want to add a flag for that just in case the DST change is revoked.

[kamara veldereth]

Yeah... thought about that too, but current DST schedule is good until 2015 so not too concerned for the time-being, but that will definitely be documented.

[Quintain]

Out of curiosity, what is your background, Quint?  You seem to know a lot about a lot... just curious as I'm impressed.

I don't have a "background" overmuch as I've simply done a lot of different shit.

I do application management on Sun Solaris servers.  Applications such as Oracle, Genesys ETLs, NGSN (basically call center control software), Weblogic, Tomcat, Apache, Apache LDAP (migrating to ->) Sun LDAP.  We are now branching into Cognos.   I do some shell scripting on Solaris servers, which is where I get info about DST for Solaris.

I've also got alot of experience in Windows related applications, but never developed any myself.  Databases include SQL Server and MySQL (most recently).  Minor Scripting on Windows 2k+ type servers, etc.

I've also done development in a mainframe environment as well as the AS/400 as well as some Visual Basic/C++.
It's just I have so many fingers in so many different pies, alot of the more generic stuff can be figured out via some googling.