Re: [sqlite] Using time and date values

[Dennis Cote]

John Stanton wrote:

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

This is a good idea, but you have the standard number wrong. It should 
be ISO 8601. See 
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for 
more details.

HTH
Dennis Cote