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:
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?
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.
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.
Joined: Feb 11, 2011
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.
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.
Joined: Feb 11, 2011
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)
This then results in a series of SQL statements along the lines of:
Not pretty, but it works. And then from there my original code changed to: