Why isn't my time formatting working?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Why isn't my time formatting working?

Rob Richardson-3
Hello!

I have a table with times stored as strings.  I massaged them into a form that strftime() should be able to work with, but it's not working.  Here's a little query using the string as it is currently formatted:

select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

This query returns null.  Why?  

(Hmmm...  I see that it should return July 3rd, 2017 when I want March 7th, but that doesn't explain why it doesn't give me anything.)

Thanks for your help.

RobR
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Richard Hipp-3
On 3/8/17, Rob Richardson <[hidden email]> wrote:
> Hello!
>
> I have a table with times stored as strings.  I massaged them into a form
> that strftime() should be able to work with, but it's not working.  Here's a
> little query using the string as it is currently formatted:
>
> select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

SQLite uses ISO-8601 dates:  YYYY-MM-DD
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Rob Richardson-3
But the strftime() function is supposed to work with whatever format I give it, isn't it?  According to the documentation, %d is a two-digit day, %m is a two-digit month, and so on.   Is there truly no way to convert my original string into a datetime object?

RobR

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, March 08, 2017 2:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

On 3/8/17, Rob Richardson <[hidden email]> wrote:
> Hello!
>
> I have a table with times stored as strings.  I massaged them into a
> form that strftime() should be able to work with, but it's not
> working.  Here's a little query using the string as it is currently formatted:
>
> select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

SQLite uses ISO-8601 dates:  YYYY-MM-DD
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Donald Griggs
Hi Rob,

The format string of '%d/%m/%Y %H:%M:%S' describes what you want as output,
not what you're supplying as input.

You can use substr() and concatenation || to mash up your original string
into the ISO format (which is much easier to handle anyway.)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Simon Slavin-3
In reply to this post by Rob Richardson-3

On 8 Mar 2017, at 7:33pm, Rob Richardson <[hidden email]> wrote:

> But the strftime() function is supposed to work with whatever format I give it, isn't it?

No.  How could that work for a date like 3/4/2017 ?  It wouldn’t know if that was the 3rd of April or the 4th of March.

> According to the documentation, %d is a two-digit day, %m is a two-digit month, and so on.   Is there truly no way to convert my original string into a datetime object?

You can do it using substrings and concatenation, but there’s no simple way.  And the text format you provided should not be stored in a database because it’s not sortable.  The year must be first, then month, then day, etc. otherwise there’s no way to sort a column into date order without picking the various parts out and arranging them into a different order.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

jose isaias cabrera-3
In reply to this post by Rob Richardson-3


Rob Richardson  wrote...

> But the strftime() function is supposed to work with whatever format I
> give it, isn't it?
>  According to the documentation, %d is a two-digit day, %m is a two-digit
> month, and so
> on.   Is there truly no way to convert my original string into a datetime
> object?

I don't know about that, but this works,

sqlite> select strftime('%d/%m/%Y %H:%M:%S', '2017-03-07 13:06:03');
07/03/2017 13:06:03

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On
Behalf Of Richard Hipp
Sent: Wednesday, March 08, 2017 2:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

On 3/8/17, Rob Richardson <[hidden email]> wrote:
> Hello!
>
> I have a table with times stored as strings.  I massaged them into a
> form that strftime() should be able to work with, but it's not
> working.  Here's a little query using the string as it is currently
> formatted:
>
> select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

SQLite uses ISO-8601 dates:  YYYY-MM-DD
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

David Raymond
In reply to this post by Rob Richardson-3
str<f>time is for formatting a standard format into whatever format you give it. You're thinking of str<p>time which would parse a given string based on your format. SQLite includes the format function, but not the parse one.



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Rob Richardson
Sent: Wednesday, March 08, 2017 2:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

But the strftime() function is supposed to work with whatever format I give it, isn't it?  According to the documentation, %d is a two-digit day, %m is a two-digit month, and so on.   Is there truly no way to convert my original string into a datetime object?

RobR

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Rob Richardson-3
In reply to this post by Donald Griggs
Thank you.

The https://www.sqlite.org/lang_datefunc.html page doesn't seem to make it clear what strftime() returns.  The specification it gives for strftime() is:

strftime(format, timestring, modifier, modifier, ...)

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.  It did not seem reasonable to me to merely pass in a string of a forced format, the ISO standard format Dr. Hipp mentioned, to get a string in some other format.  

But I've been burned before by data types, or lack thereof, in SQLite.  I usually work in C# and PostgreSQL, where variables and data columns always have definite data types, and, if I remember correctly (it's been a couple of years since I worked with SQLite), SQLite does things differently.  I know there's nothing stopping me from putting any value into a field, regardless of the type of data other records have for that field.

RobR

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Donald Griggs
Sent: Wednesday, March 08, 2017 2:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

Hi Rob,

The format string of '%d/%m/%Y %H:%M:%S' describes what you want as output, not what you're supplying as input.

You can use substr() and concatenation || to mash up your original string into the ISO format (which is much easier to handle anyway.) _______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Jens Alfke-2

> On Mar 8, 2017, at 11:59 AM, Rob Richardson <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

David Raymond
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:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Paul Sanderson
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.

Interestingly I rarely see dates stored in ISO8601 format/text



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 <[hidden email]> 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:[hidden email]]
> 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 <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

R Smith
In reply to this post by Rob Richardson-3


On 2017/03/08 9:59 PM, Rob Richardson wrote:
> Thank you.
>
> The https://www.sqlite.org/lang_datefunc.html page doesn't seem to make it clear what strftime() returns.  The specification it gives for strftime() is:
>
> strftime(format, timestring, modifier, modifier, ...)

It returns a string, they all return strings. There is no "lack" of a
type, there is a lack of an object, but this isn't C.  The "str" in the
name "strftime()" indicates it is returning a string, in the same way
the "f" indicates it is a formatted one at that, and the last part of
the name gives a clue as to what it will be returning the formatted
string of.

Here's a good list of format specifiers:
http://www.faximum.com/manual.d/client.server.d/manpages.23.html

> 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.  It did not seem reasonable to me to merely pass in a string of a forced format, the ISO standard format Dr. Hipp mentioned, to get a string in some other format.

Well yeah, it makes no sense if you do it like that... but keeping in
mind that function is mostly used in a Data Query where the input is a
standard field, the output could be anything your heart desires - which
is when this starts making sense.

> But I've been burned before by data types, or lack thereof, in SQLite.  I usually work in C# and PostgreSQL, where variables and data columns always have definite data types, and, if I remember correctly (it's been a couple of years since I worked with SQLite), SQLite does things differently.

Quite correct, but in no way does it do things willy-nilly. It's "weird"
way is very documented, tested to infinity and completely accurate and
reproducible. (Sometimes people confuse duck-typing with loose-canon
shenanigans). And yes - expecting an integer and finding a string in a
field can be surprising, but every language has its quirks, and most
people make that mistake only once. :)

Btw - we're all PostgreSQL fans too.

Please ask if you have any more questions and good luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

R Smith
In reply to this post by Paul Sanderson

On 2017/03/08 10:40 PM, Paul Sanderson 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.
>
> Interestingly I rarely see dates stored in ISO8601 format/text

Because every programmer is a self-proclaimed optimization genius!

When speed and space counts, I too opt for the Unix epochs or nano
seconds, but the luxury of being able to understand a human-readable
date and time exactly right from viewing it in your favourite DB manager
or even CLI, is priceless. It saves so much time when debugging or
looking for a problem or fixing a client's mistake some time after it
all gone live and you have forgotten all about the internals of the system.

If speed/space isn't critical, I always advise ISO8601 dates, typically
stored (in SQLite anyway) in a NUMERIC typed column.
NUMERIC is suggested by the documentation for dates, but I am unsure if
it  gains any actual optimized nature over a STRING.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Tim Streater-3
In reply to this post by Rob Richardson-3
On 08 Mar 2017 at 20:40, Paul Sanderson <[hidden email]> 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.

This is also what I do; seconds since the epoch. I can't imagine string dates or times as strings. You're also going to need to give the user the option to select their date/time format, too, so converting to display should be done at display time, not before.

--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Paul Sanderson
In reply to this post by Rob Richardson-3
Oops - typo in my post above. Unix is of course secs since 1970.

and Tim yes I too always use numerical dates - each to their own though -
my post wasn't trying to say what is best, just what I see.

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 22:57, Tim Streater <[hidden email]> wrote:

> On 08 Mar 2017 at 20:40, Paul Sanderson <[hidden email]>
> 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.
>
> This is also what I do; seconds since the epoch. I can't imagine string
> dates or times as strings. You're also going to need to give the user the
> option to select their date/time format, too, so converting to display
> should be done at display time, not before.
>
> --
> Cheers  --  Tim
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Keith Medcalf
In reply to this post by Paul Sanderson

On Wednesday, 8 March, 2017 13:40, Paul Sanderson <[hidden email]> 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 <[hidden email]> 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:[hidden email]]
> > 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 <[hidden email]>
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Keith Medcalf

Ooops.  .NET Epoch is UTC based.  OLE Time is localtime based.

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> 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
> <[hidden email]> 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 <[hidden email]>
> 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-
> [hidden email]]
> > > 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
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Will Parsons
In reply to this post by Paul Sanderson
On Wednesday,  8 Mar 2017  3:40 PM -0500, Paul Sanderson 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.

I don't know a about "chrome dates" or "NSDates/MacAbsolute", but the
others are *time* formats, not dates.  Sure, one can use a time format
to represent a date (presumably by using midnight to represent the
date), but then you should probably add a constraint to database
allowing only multiples of 86400 seconds in the field.

Perhaps this may seem a bit of a quibble, but dates are a conceptually
distinct from timestamps.

> Interestingly I rarely see dates stored in ISO8601 format/text

I don't know about that - I certainly do.

> 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 <[hidden email]> 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."

--
Will

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Jens Alfke-2
In reply to this post by R Smith

> On Mar 8, 2017, at 12:52 PM, R Smith <[hidden email]> wrote:
>
>> Interestingly I rarely see dates stored in ISO8601 format/text
>
> Because every programmer is a self-proclaimed optimization genius!

In this case it often makes sense to optimize in advance. In multiple situations over the years I’ve seen date-string parsing be a major bottleneck, in operations like database indexing and file reading. It’s surprisingly expensive; some of that is due to handling the weirdnesses of human date systems, but a lot seems to be because the typical functions have to handle arbitrary formats and decipher the format string as well as the input. (I’ve found you can do a lot better with a function that’s hardcoded to parse a specific date format.)

> If speed/space isn't critical, I always advise ISO8601 dates, typically stored (in SQLite anyway) in a NUMERIC typed column.

I basically agree, it’s just that the speed seems to be critical more often than one would think :)

At least some date formats, including ISO-8601 with times in UTC, have the feature that you can compare dates as strings without having to parse them. That makes sorting by date a lot faster.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why isn't my time formatting working?

Michael Falconer
Um....I'm one of those sad old hacks who doesn't store dates at all. Just
Years, Months, and Days. You can do just about anything with them stored
that way, and in sqlite it seems to matter little whether they are string
or numeric columns. Dates are a rubbery concept not well suited to db
storage IMHO. ;-)


On 11 March 2017 at 05:35, Jens Alfke <[hidden email]> wrote:

>
> > On Mar 8, 2017, at 12:52 PM, R Smith <[hidden email]> wrote:
> >
> >> Interestingly I rarely see dates stored in ISO8601 format/text
> >
> > Because every programmer is a self-proclaimed optimization genius!
>
> In this case it often makes sense to optimize in advance. In multiple
> situations over the years I’ve seen date-string parsing be a major
> bottleneck, in operations like database indexing and file reading. It’s
> surprisingly expensive; some of that is due to handling the weirdnesses of
> human date systems, but a lot seems to be because the typical functions
> have to handle arbitrary formats and decipher the format string as well as
> the input. (I’ve found you can do a lot better with a function that’s
> hardcoded to parse a specific date format.)
>
> > If speed/space isn't critical, I always advise ISO8601 dates, typically
> stored (in SQLite anyway) in a NUMERIC typed column.
>
> I basically agree, it’s just that the speed seems to be critical more
> often than one would think :)
>
> At least some date formats, including ISO-8601 with times in UTC, have the
> feature that you can compare dates as strings without having to parse them.
> That makes sorting by date a lot faster.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Regards,
     Michael.j.Falconer.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...