The sqlite3 timestamp conversion between unixepoch and localtime can't be done according to the timezone setting on the machine automatically.
hongy...@gmail.com
hongyi.zhao at gmail.com
Tue Aug 31 01:10:33 EDT 2021
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/
More information about the Python-list
mailing list