localtime on current_time differs from localtime on current_timestamp

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

localtime on current_time differs from localtime on current_timestamp

PALAMARA Alain
Hello,

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?

To reproduce it, here is the output of my complete test:

sqlite>select time(current_timestamp), time(current_time), time('now');
time(current_timestamp)  time(current_time)  time('now')
-----------------------  ------------------  -----------
10:47:40                 10:47:40            10:47:40

sqlite>select time(current_timestamp, 'localtime'), time(current_time, 'localtime'), time('now','localtime');
time(current_timestamp, 'localtime')  time(current_time, 'localtime')  time('now','localtime')
------------------------------------  -------------------------------  -----------------------
12:47:44                              11:47:44                         12:47:44

As you can see, the localtime for current_time is one hour shifted from the two others despite the "raw" results are the same.

Regards,
Alain


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: localtime on current_time differs from localtime on current_timestamp

Keith Medcalf
On Thursday, 26 September, 2019 05:33, PALAMARA Alain <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: localtime on current_time differs from localtime on current_timestamp

Adrian Ho
On 26/9/19 11:51 PM, Keith Medcalf wrote:
> 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).

I must remember that phrase, "whim of the politicians". Such a beautiful
hand-wavy description of perfectly mundane issues like daylight saving time.

Given the magnitude of the difference (1 hour) and the seasonal
differences between Jan 1 and the date of the original question (Sep
26), I'd surmise this was the most likely cause.

--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users