Re: [sqlite] Why isn't my time formatting working?

UmI'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

Re: [sqlite] Why isn't my time formatting working?

> On Mar 8, 2017, at 12:52 PM, R Smith 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

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

t; > > > > > > 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') > > > > > > >

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

On 08 Mar 2017 at 20:40, 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

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

ine 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 Jen

Re: [sqlite] Why isn't my time formatting working?

ehalf 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

Re: [sqlite] Why isn't my time formatting working?

> On Mar 8, 2017, at 11:59 AM, Rob Richardson 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

Re: [sqlite] Why isn't my time formatting working?

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

Re: [sqlite] Why isn't my time formatting working?

...@mailinglists.sqlite.org] 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

Re: [sqlite] Why isn't my time formatting working?

PM To: SQLite mailing list Subject: Re: [sqlite] Why isn't my time formatting working? On 3/8/17, Rob Richardson <rdrichard...@rad-con.com> 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

Re: [sqlite] Why isn't my time formatting working?

On 8 Mar 2017, at 7:33pm, Rob Richardson 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. >

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.)

Re: [sqlite] Why isn't my time formatting working?

- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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 <rdrichard...@rad-con.com> wrote: > Hel

Re: [sqlite] Why isn't my time formatting working?

On 3/8/17, Rob Richardson 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

[sqlite] Why isn't my time formatting working?

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