Ooops. .NET Epoch is UTC based. OLE Time is localtime based. > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Keith Medcalf > Sent: Wednesday, 8 March, 2017 18:32 > To: SQLite mailing list > Subject: Re: [sqlite] Why isn't my time formatting working? > > > On Wednesday, 8 March, 2017 13:40, Paul Sanderson > <sandersonforens...@gmail.com> wrote: > > > The vast majority of dates I see in SQLite databases are unix epoch > > integer times (seconds since 1/1/1980) with unix milli seconds a > > close second. > > > Efficient to store, sort and do date arithmetic on but need to be > > converted to display. > > > I also see unix nano seconds, 100 nano seconds, windows filetimes, > chrome > > dates and NSDates/MacAbsolute very regularly. > > Don't forget Julian, Reduced Julian, Modified Julian, Rata Die, Rata Die > Month, NTP32, NTP64, ANSI Epoch, GPS Epoch, or .NET Epoch. There are > probably more, but these plus the ones you listed are the only ones I know > how to convert to and fro. > > > Interestingly I rarely see dates stored in ISO8601 format/text > > Because it takes more space and unless you take special precautions to > handle localization then getting timezone support and sorting is a bit of > a dog, unless you always store ISO8601 datetimes in UTC. All the other > formats are based on UTC (with the exception of .NET Epoch and the version > of ANSI Epoch time used in a bunch of their Microsoft products which are > offsets from the localtime epoch). > > In either case, if you store the datetime in UTC (even if it is ISO8601) > you will generally have to convert to localized time for display anyway, > so using whatever format is most efficient for your needs is often the way > to go. > > > Paul > > www.sandersonforensics.com > > skype: r3scue193 > > twitter: @sandersonforens > > Tel +44 (0)1326 572786 > > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic- > > Toolkit > > -Forensic Toolkit for SQLite > > email from a work address for a fully functional demo licence > > > > On 8 March 2017 at 20:17, David Raymond <david.raym...@tomtom.com> > wrote: > > > > > Correct. The ISO strings are the de-facto standard since that's what > all > > > the date and time functions take in. > > > http://www.sqlite.org/lang_datefunc.html > > > > > > "The strftime() routine returns the date formatted according to the > > format > > > string specified as the first argument." > > > > > > It's there so you can store your datetimes in a standardized way, then > > > display them however you or your user wants, be it > > > "03/07/2017" > > > "3/7/17" > > > "7-Mar-2017" > > > "20170307" > > > "March 7, 2017 AD" > > > "The 7th day of the third month of the 17th year of the reign of > > Tiberius > > > Caesar" > > > > > > The last one would be more in line with the modifiers you can use. > > > > > > strftime('%m/%d/%Y', TiberiusCaesar, 'start of reign', '+17 years', > > 'start > > > of year', '+3 months', '+7 days') > > > > > > > > > -----Original Message----- > > > From: sqlite-users [mailto:sqlite-users- > boun...@mailinglists.sqlite.org] > > > On Behalf Of Jens Alfke > > > Sent: Wednesday, March 08, 2017 3:04 PM > > > To: SQLite mailing list > > > Subject: Re: [sqlite] Why isn't my time formatting working? > > > > > > > > > > On Mar 8, 2017, at 11:59 AM, Rob Richardson <RDRichardson@rad- > con.com> > > > wrote: > > > > > > > > Given the lack of an indication of the return type, it seemed to me > to > > > be reasonable to assume that since I'm passing in a string as one of > the > > > arguments, I'd get a datetime object out. > > > > > > SQLite doesn’t have a datetime type, as far as I know. Dates are > stored > > as > > > strings. > > > > > > —Jens > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
