Dealing with results of java time code

[Beverley Moy]

Hi there,

I hope that you can help me -- this isn't exactly a java question.

We have a java application written by contractors that stores the timestamp for various activities in our MS SQL database in java's long format.

I have a sql stored procedure that is supposed to take that long and convert it into a sql datetime. However, there is something wrong with the stored procedure, as it converts it into Pacific Standard Time or Pacific Daylight Savings Time depending on which time we are on CURRENTLY, rather than what time it was when the data was actually stored.

So, for example, I have a java date of 1280264615065. Right now, when I run my SQL proc it tells me that converts to 2010-07-27 13:03:35.067 -- but I know it actually was 14:03 on that day. If I was to wait for the time change and run the proc again, I would get the correct result.... but anything that occurred today would now be converted incorrectly.

This is the sql code:

ALTER FUNCTION [dbo].[DateFromLong](@ljd AS BIGINT) RETURNS DATETIME AS BEGIN DECLARE @retval DATETIME, @offset INTEGER, @seconds INTEGER, @millis INTEGER SELECT @offset = 28800 SET @seconds = (@ljd /1000) - @offset; SET @millis = @ljd % 1000 SET @retval = '1 jan 1970 00:00.000' SET @retval = DateADD(s, @seconds, @retval) ; SET @retval = DateADD(ms, @millis, @retval) ; RETURN @retval END

My guess is that the offset is wrong and I need to determine the correct offset to use by first inspecting @ljd and deciding whether that occurred during Daylight Savings Time or not. Or, by going through the conversion, then checking the outcome, and then adding or subtracting an hour to the result. But both seem to be bad solutions....

Any ideas? Should I go away and ask this in a sql forum instead?

Thanks for any help you can provide.

Beverley

[Jesper de Jong]

Welcome to the Ranch.

The numerical value that you got from Java is a number of milliseconds since 01-01-1970 00:00:00 UTC (it's in the UTC timezone). So, that is how you have to interpret that value. Don't interpret it as if it is in Pacific Standard Time, Pacific Daylight Savings Time or any other time zone.

I'm not an SQL expert so I don't know exactly how you'd do that in MS SQL. But I hope this gives you a useful hint about what to look for.

[Paul Clapham]

You could ask the question in an SQL forum, but my suspicion is that the SQL built-in functions don't deal with timezones at all. It would be much easier to write Java code to convert the long value to a timestamp.

[Beverley Moy]

It's only easier to write Java code if that's what you're already dealing with I'm writing reports, so I'm in SQL code already.... Can't make changes to the java app.

I'm thinking it is going to have to be a reworked version of my original code, but once I have the long number converted to a UTC time, then deal with whether it it UTC-7 or UTC-8 at that particular moment in time. Which means knowing if it's before 2007 or after, since that's when it changed last.

Luckily the data only goes back to around then so I don't have to worry about the 1987 time change when it apparently last was monkeyed with.

[Paul Clapham]

I see... so the SQL functions do know about and use timezones, just not the historical aspects of the timezones. So yeah, you're going to have to work around that deficiency yourself.

[Beverley Moy]

Thanks for your help Jesper & Paul, it got me thinking in the right direction.

It occurred to me I already have a "dates" table, so I added a UTCOffset column to that table, then populated it with -7 or -8 depending on whether each date was within DST or standard time. Then I removed the @offset part of the original calculation at the top of the stored proc and replaced it with a select from that table to see whether to subtract 7 or 8 hours from that particular date. Seems to be working fine.

Good thing I only have to deal with a single time zone

So as not to leave a half-answer for anyone in future dealing with the same problem, here's the code I used to determine the start & end of DST for each year from 1987 onward. (Assumes a pre-populated table called AllDates1950to2050 that contains a date table listing every possible date in that timeframe)

--From 1987 to 2006, daylights savings ran from the first Sunday in April until the last Sunday in October. Select year(date) as TheYear, 'Update alldates1950to2050 set utcoffset = -7 where date >= ''' + cast( DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-04-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-04-01'), 0) ) as varchar(11)) + ''' and date < ''' + cast(DateAdd(day,-7, DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)) ) as varchar(11)) + '''; Update alldates1950to2050 set utcoffset = -8 where date >= ''' + cast(DateAdd(day,-7, DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)) ) as varchar(11)) + ''' and date < ''' + cast( DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date)+1 as varchar(4)) + '-04-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date)+1 as varchar(4)) + '-04-01'), 0) ) as varchar(11)) + '''' as TheCode from alldates1950to2050 where year(date) between 1987 and 2006 and month(date) = 1 and day(date) = 1 -- Starting in 2007, daylight savings ran from the second Sunday in March to the first Sunday in November Select year(date) as TheYear, 'Update alldates1950to2050 set utcoffset = -7 where date >= ''' + cast(DateAdd(day, 7, DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-03-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-03-01'), 0)) ) as varchar(11)) + ''' and date < ''' + cast(DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0) ) as varchar(11)) + '''; Update alldates1950to2050 set utcoffset = -8 where date >= ''' + cast(DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date) as varchar(4)) + '-11-01'), 0) ) as varchar(11)) + ''' and date < ''' + cast(DateAdd(day, 7, DateAdd(day, (8-DatePart(weekday, DateAdd(Month, DateDiff(Month, 0, cast(year(date)+1 as varchar(4)) + '-03-01'), 0)))%7, DateAdd(Month, DateDiff(Month, 0, cast(year(date)+1 as varchar(4)) + '-03-01'), 0)) ) as varchar(11)) + '''' as TheCode from alldates1950to2050 where year(date) >= 2007 and month(date) = 1 and day(date) = 1 order by 1

This then results in a series of SQL statements along the lines of:
Update alldates1950to2050 set utcoffset = -7 where date >= 'Mar 11 2007' and date < 'Nov 4 2007'; Update alldates1950to2050 set utcoffset = -8 where date >= 'Nov 4 2007' and date < 'Mar 9 2008'

Not pretty, but it works. And then from there my original code changed to:
-- Convert the java long "date" into a UTC datetime field SET @seconds = (@ljd /1000) SET @millis = @ljd % 1000 SET @retval = '1 jan 1970 00:00.000' SET @retval = DateADD(s, @seconds, @retval) ; SET @retval = DateADD(ms, @millis, @retval) ; --select @retval as DateTimeInUTC -- Now, determine whether this was during PST or PDT time and adjust accordingly select @offset = isnull(UTCOffset,0) from AllDates1950to2050 where date = CONVERT(DATETIME, CONVERT(INT, @retval + 0.5)) - 1 set @retval = dateAdd(hh, @offset, @retval)