Hello,

I'm working on a project where the database is embedded in the application and distributed to clients. I have a concern related to dates/times that I'd like to put out there.

I'm thinking that dates and times should be stored in some locale neutral way. This is because as a distributed application, the database is very likely to change timezones much more frequently than a traditional client/server model. For instance if the software is running on a laptop, one day the database may be in San Diego, the next day it may be in Japan.

How should I store the date? Should I convert it to GMT:00 before it goes to the db? Is it better to use an "Epoch" and just store the date as a long?

My answer is to use GMT... You can easily convert local time to GMT at the point of capture, but there is always uncertainty from that moment onward. Once you have the data stored using GMT for times and dates, you can easily convert it back to local time for presentation, regardless of what "local time" is at the moment!

-PatP

Ok. Thanks! Helps to know I'm thinking of the problem in the right way.

Let me bounce another crazy idea off of you. I'm using Java, and will almost certainly continue using Java. In case you're not familiar, the Date object is basically a wrapper around a long value representing milliseconds from "00:00:00 GMT on January 1, 1970"

I'm thinking about just storing the long value instead of enduring all of the date conversion BS. Like you said, it's more a matter of how I spit it back out to end user. To my knowledge, this really only changes the DB schema from TIMESTAMP (I'm using Derby) to a long value. I think queries/sorting would still be pretty natural since the long value still represents a fairly distinct point in time.

I'm a little unsure of myself here because I'm wondering if there are other advantages to using the database TIMESTAMP that I'm not considering. Like I said however, I think it is probably more simple than date math in Java.

The primary advantage that I see in using the TIMESTAMP datatype is that there is no reasonable explanation for mistaking it for anything else... Nobody can reasonably claim "It was a long, so I did math with it" and not understand the consequences.

If and when you need to convert to another database engine (any program that lives long enough will have to swtich database engines, it is a law of nature), the TIMESTAMP datatype almost always makes that conversion more straightforward.

-PatP

Cool. I hear you on the concern about mistaking the integer value for something else. That's a very legitimate point.

Not sure I can think of any scenarios where converting a long value between database engines would be that tough. Would you be kind enough to elaborate?

If you are manually doing the conversion, one column at a time, then there is no problem. I'm thinking about when you need to convert lots of columns (say over 250,000 columns), like in a PeopleSoft database.

Automated tools have a hard time distinguishing between an I8, an *ix ephoch, and a GIS locator code even though all of them are stored in a 64 bit integer. You could simply "move" the columns to the new database engine, but you'd need a pretty sophisticated ETL tool to read or infer enough metadata to handle them correctly if you wanted to do anything more than just "pick 'em up an' put 'em down" as you went from one database engine to another.

-PatP