The sqlite3 timestamp conversion between unixepoch and localtime can't be done according to the timezone setting on the machine automatically.

[Dennis Lee Bieber]

On Sun, 29 Aug 2021 19:49:19 -0700 (PDT), "hongy... at gmail.com"
<hongyi.zhao at gmail.com> declaimed the following:

>On Ubuntu 20.04.2 LTS, I use the [recent2](https://github.com/dotslash/recent2/blob/master/recent2.py) to log and query my bash history, which uses a sqlite3 database to store the history information. The `datetime` of running a command is inserted in the sqlite3 database with [a `unixepoch` format](https://github.com/dotslash/recent2/blob/f1018aee228c710cc7d1b8b93bc0228791a54563/recent2.py#L45), and it will be converted into `localtime` accordingly when retrieved and displayed later.
>

	As I read it, it is interpreting whatever value was provided as
UNIXEPOCH when storing the value -- but stores it as an ISO date/time
string! https://www.sqlite.org/lang_datefunc.html

sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
sqlite>
sqlite> select datetime('now');
2021-08-30 22:28:58
sqlite>

	I can't see anything in that code listing that explicitly manipulates
the date/time when fetched for output. Nor do I see the connection
specifying Python adapter usage:
https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters
so I'd expect the output to be in ISO UTC format; the native result of
using SQLite3's datetime()..

"""
            if not args.hide_time:
                cmd_time = row_dict["command_dt"]
                if args.time_first:
                    print(f'{Term.YELLOW}{cmd_time}{Term.ENDC}
{colored_cmd}')
                else:
                    padded_cmd = pad(raw_text=row_dict['command'],
print_text=colored_cmd)
                    print(f'{padded_cmd} # rtime@
{Term.YELLOW}{cmd_time}{Term.ENDC}')
"""	
>But I found that it did not perform the correct conversion according to the time zone setting on the machine, as shown below:
>```shell
>werner at X10DAi-00:~$ rm 222222222222222222
>rm: cannot remove '222222222222222222': No such file or directory
>werner at X10DAi-00:~$ recent -fo -w . 2222222222222
>rm 222222222222222222                              # rtime@ 2021-08-29 10:57:13
>werner at X10DAi-00:~$ date 
>Sun 29 Aug 2021 06:57:22 PM CST
>```

	Might have helped to mention you were in China... To me, CST is North
America Central Standard Time (and I'd have expected this time of year to
see CDT - Central Daylight Time)... That led me on a weird meaningless side
track...

	What documentation do you have that says it will display the date/time
in local timezone? (The README appears to be incorrect -- the utility logs
unix epoch [UTC seconds since 1970] AS ISO UTC string).

sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
sqlite> select datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 14:51:06
sqlite>
sqlite> select datetime('now', 'localtime');
2021-08-30 18:50:19
sqlite> select datetime('now');
2021-08-30 22:50:32
sqlite>

	I'm in EDT (Eastern Daylight Time) -- so 4 hours behind UTC.


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed at ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/