8 replies to this topic

[Lance]

For example, if I have a modified_date field in table T, how do I select those with modified_date within most recent 5 days?

Is there other utility function to convert a datetime to a float number?

Thank you for your help!

[WingedPanther]

For most SQL databases, you would do something like:

SELECT * FROM T WHERE MODIFIED_DATE >= [five days ago]

Usually, I do conversions within the language I'm using to access the database.

[Lance]

Thanks Panther.

That's not exactly what I want. The thing is the date field is actually treated as a string. so '2009-3-15' will be greater than '2009-03-15', etc. I was wondering if there was a way to treated it as TIMESTAMP/DATETIME.

Is is because Sqlite3 doesn't have native datetime datatype support?

And yes, your proposal should work if I make sure datetime values always be formatted as yyyy-mm-dd hh:MM:ss before insert into a table.

[WingedPanther]

I would approach it differently (having just done some reading). I would store DATETIMEs as floating point values, where the integer part is the number of days after a certain date, and the decimal part is the fraction of the day. Since many languages store datetime values with this approach anyway, I think it may be your best approach.

[Lance]

Great idea!!

Should satisfy my needs. Thanks a lot!

[Lance]

I would have Rep+'d /Credit+'d you but I don't know how to :(

[WingedPanther]

Just use the little scale icon next to the #4(permalink)

[Lance]

Done.

[Orjan]

or there is the unix method of storing time, an long integer with seconds since a certain time (1970-01-01 00:00:00). most programming languages has built-in support for converting this.