> *if* you know that the number *is* a date.
If the column has a type of timestamp, it should be safe to always
assume that it IS a date. Don't put non-dates in it, and there isn't a
problem. I can't imagine a case where you would not know whether the
value is a date. Am I missing something here?
The datetime() function will accept Julian or string representations
(including special strings like 'now') so even if you did something
really nasty like store dates sometimes in Julian format and sometimes
as strings, the date and time functions will STILL get things right,
though sorting would be a mess and indexes would be useless.
> This is fine as long as you always view your data with
> application-specific tools and never with generic ones.
Julian *is* the preferred internal format for dates in SQLite. There is
nothing application specific about this. I actually tested the queries
in a generic viewer. I used SELECT Julian('now'); in sqlite3Explorer to
get the "2455133.71759947" value used in my original example.
> it's still worth mentioning that [timezones] can be
> part of the parameter.
You are right of course. They should have been mentioned in the
documentation.
John
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Bishop
Sent: Thursday, October 29, 2009 3:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Late data typing. Am I missing something?
John Crenshaw wrote:
> Strings have a number of other disadvantages in this case. They take
> more computations to compare, they take time to parse when you read
> them, and they take longer to build when you insert them. Generally,
> storing dates as a number of some sort is ideal.
>
I do agree with that. The problem is that the ideal way to store a date
is different from the ideal way to *display* a date. And that the
conversion between the two has to be done manually.
> Building a query to return the value as a human readable string is
> fairly easy:
> SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20'
>
Yes, it's easy to do that -- *if* you know that the number *is* a date.
This is fine as long as you always view your data with
application-specific tools and never with generic ones.
> I imagine the timezones aren't documented, because they aren't
actually
> stored if the Julian format is used internally (they have to be
> converted to get the Julian in UTC.) If you use a string, it can store
> the timezone I guess, but it will cost you in terms of speed.
That's not the point. Timezones can't be part of the return value of
the strftime/datetime/julianday functions, but it's still worth
mentioning that they can be part of the parameter.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
