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

[hongy...@gmail.com]

On Tuesday, August 31, 2021 at 7:55:51 AM UTC+8, Dennis Lee Bieber wrote:
> On Sun, 29 Aug 2021 19:49:19 -0700 (PDT), "hongy... at gmail.com" 
> <hongy... 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"] 

Thank you very much. Based on your above comments and the discussion on <https://stackoverflow.com/questions/4770297/convert-utc-datetime-string-to-local-datetime>, I fixed this problem by the following method:

# Install and import some necessary packages: 
from datetime import datetime
# pip install python-dateutil
from dateutil import tz

 
Then use the following codes to do the trick:

                from_zone = tz.tzutc()
                to_zone = tz.tzlocal()
                cmd_time = row_dict["command_dt"]
                cmd_time = datetime.strptime(cmd_time, '%Y-%m-%d %H:%M:%S').replace(tzinfo=from_zone).astimezone(to_zone).strftime("%Y-%m-%d %H:%M:%S")


Best, Hongyi

> 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 
> wlf... at ix.netcom.com http://wlfraed.microdiversity.freeddns.org/