On Thursday, 26 September, 2019 05:33, PALAMARA Alain
<[email protected]> wrote:
>I'm quite new to sqlite and I'm surprised about the result I got from
>executing time(current_time, 'localtime') function.
>I use the sqlite3 command line (version 3.29.0) on Windows 7 and I tried
>to get my local time using this simple
>statement:
> select time(current_time, 'localtime');
>and the result was wrong. I did tried with:
> select time(current_timestamp, 'localtime');
>and the result was right.
>Is that behavior normal or is that a bug?
That would be expected behaviour, depending on the whim of politicians for the
last 20 years at your location.
CURRENT_TIME is syntactic sugar (simply a different spelling for) time('now'),
and CURRENT_TIMESTAMP is syntactic sugar for datetime('now'). So the former
(CURRENT_TIME) only yields the time part of the current datetime, whereas
CURRENT_TIMESTAMP returns the entire current date and time (both in UT1).
The function time(...) is the same as the function datetime(...) except that it
only returns the time part of the datetime and discards the date part (and the
date(...) function takes exactly the same but only returns the date part,
discarding the time part). Nevertheless, the datetime(...) functions require a
whole date and time on which to operate. If you do not provide one, then one
is provided for you. If you omit the time part, then the default time part is
00:00:00.000. If you omit the date part, then the default date is 2000-01-01.
So, when you ask for time(current_time, 'localtime') you are saying to get the
current utc datetime, discard the date part, then assume that the date part is
2000-01-01 with that time, then compute the "localtime" for that UTC time, and
then discard the date part and return the result. So the answer is correct,
but for 2000-01-01 according to the whims of the politicians who set the
localtime rules at that time (and whether or not your OS knows how to compute
that, Windows does not, for example).
So what you really want is to use the datetime functions directly, as in:
select time('now', 'localtime');
which will give you the date and time in UTC, convert it into 'localtime', and
then discard the date part, returning only the time part.
current_date, current_time, current_timestamp are merely alternate spellings
for date('now'), time('now'), and datetime('now') respectively.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
