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