On Thursday, 26 September, 2019 05:33, PALAMARA Alain 
<alain.palam...@secheron.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to