[EMAIL PROTECTED] wrote:
This isn't a bug.
The magic current_timestamp keyword is really an alias for
"datetime('now')". And datetime('now') returns you a text
string in the format "YY-MM-DD HH:MM:SS". Yes, this means
that the seconds have been rounded to the nearest whole
second. But that is what current_timestamp keyword is
documented to do. Imagine the outcry and all the broken code
if current_timestamp suddenly started returning dates
in the format "YY-MM-DD HH:MM:SS.SSS".
Would could think about adding a new magic keyword,
perhaps current_hires_timestamp, that included the fractional
seconds. Such a magic keywords would be an alias
for strftime('%Y-%m-%d %H:%M:%f','now'). But adding this
new keyword will be a hard sell since there is a lot of
emphasis on avoiding code bloat. So for now, you are better
off just using the strftime function if you need a high
resolution timestamp.
Actually, current_timestamp (and datetime) returns the full 4 digit year.
The problem is that you can't use a function like strftime as the
default value for a column when you create a tbale. It only accepts
NULL, a string constant, a number, or one of the magic current_* values.
Or (and better in my view) store
your dates as julian day numbers and convert to a ISO8601
for display only.
I thought that was what the current_timestamp did!
You are saying that SQLite really stores the 19 byte date and time
string instead of the 8 byte julian day number as a floating point
double. This seems awfully wasteful. I can see where this would be of
benefit to systems without floating point, so maybe there is a good
reason for this, but it is not what I expected.
I suspect a few others might be expecting finer resolution from the
current_timestamp when used to add a create or modified timestamp to a
record. The current behavior seems to increase the likelyhood of
timestamp conflicts in such applications.
I would suggest that adding a small amount of code bloat to accept
another magic default value that stores the high resolution julian day
number would be worth the cost for both database size reduction and
increased resolution. The user can always for this time anyway they like
for display in the select statements where arbitrary functions are
allowed. I think the best name (current_timestamp) is already taken, so
I would suggest julian_timestamp. It would have been better if the
original magic values were current_date, current_time, and
current_datetime, then the new value would logically be the
current_timestamp, but that can't be changed now.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
