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 <rdrichard...@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
