aspose file tools*
The moose likes Beginning Java and the fly likes Dealing with results of java time code Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Dealing with results of java time code" Watch "Dealing with results of java time code" New topic
Author

Dealing with results of java time code

Beverley Moy
Greenhorn

Joined: Feb 11, 2011
Posts: 3
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:



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
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14273
    
  21

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.

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18669
    
    8

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
Greenhorn

Joined: Feb 11, 2011
Posts: 3
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
Bartender

Joined: Oct 14, 2005
Posts: 18669
    
    8

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
Greenhorn

Joined: Feb 11, 2011
Posts: 3
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:
 
Don't get me started about those stupid light bulbs.
 
subject: Dealing with results of java time code