Re: [sqlite] Using time and date values

[John Stanton]

Brad wrote:
I am looking for a simple way of using date and time values with
sqlite3. I have some Ctime classes (VC++.net) which have to be stored in
the database and they should be compared.


If the class you're using has .Value property, or a way to easily 
transform the date from one format to another, you should probably store 
that.  For instance, I like to store dates in string format, using 
something like "CCYYMMDDHHmmSS.SSSSSS". If I expect to need to deal with 
time zones, I'll store all the dates/times as GMT, or Universal time, 
and do the conversions when I create the datetime objects.


You might consider storing dates and times in ISO9601 format, and doing 
as suggested above, using Zulu time (also known as UTC, Universal 
Co-ordinated Time, formerly GMT).  By complying with a standard and 
using UTC you can present the time according to time zone and daylight 
saving status.  In a widely distributed system the complication is 
keeping track of daylight saving, but there are databases and tools 
which help.

The other way to store time is an offset from an epoch.  The problem 
there is that there are arbitrary epochs.  The best one is some date BC 
which has the magic property that it can be used to transform the offset 
into all of the major world date systems, like Gregorian, Arabic, 
Hebrew, Japanese etc.

If you application is fairly straightforward the ISO9601 format has the 
great advantage that it is human readable in its raw form.
JS