Convert SQLite timestamp to readable human

[jcpetu]

Hi people,

I'm tryng to convert SQLite timestamp to readable human format but I get 00:00:00 instead of the known value.

Here my code if someone could help:

#include <Constants.au3>
#include <Date.au3>

Local $EPOCH = 13215743943956926 / 1000000; 16/10/2019 08:59:03 p.m.
Local $EPOCHinHHMMSS= Convert($EPOCH)
ConsoleWrite($EPOCHinHHMMSS)

MsgBox($MB_SYSTEMMODAL, '', ConvertToReadable($EPOCHinHHMMSS))

Func ConvertToReadable($iEpoch)
    Local $difference = _DateDiff("s", "1601/01/01 00:00:00", "1970/01/01 00:00:00")
    Return Convert(_DateDiff("s", $iEpoch, $difference))
EndFunc   ;==>_EpochToDate

Func Convert($t)
    $h = Floor($t / 3600)
    $m = Floor(($t - $h * 3600) / 60)
    $s = Mod($t, 60)
    Return StringFormat('%02i:%02i:%02i',$h, $m, $s)
EndFunc

Thanks in advance.

[TheXman]

30 minutes ago, jcpetu said:

Local $EPOCH = 13215743943956926 / 1000000; 16/10/2019 08:59:03 p.m.

That GMT epoch value for "2019-10-06 8:59:03 p.m." is 1571259543 .  I have no idea where you got your value.

Edited December 9, 2019 by TheXman

[mikell]

$time = 1571259543 
$aCall = DllCall("msvcrt.dll", "str:cdecl", "ctime", "int*", $time)
Msgbox(0,"", $aCall[0])

$time = 13215743943956926 / 10000000 
$aCall = DllCall("msvcrt.dll", "str:cdecl", "ctime", "int*", $time)
Msgbox(0,"", $aCall[0])

[jcpetu]

2 hours ago, mikell said:

$time = 1571259543 
$aCall = DllCall("msvcrt.dll", "str:cdecl", "ctime", "int*", $time)
Msgbox(0,"", $aCall[0])

$time = 13215743943956926 / 10000000 
$aCall = DllCall("msvcrt.dll", "str:cdecl", "ctime", "int*", $time)
Msgbox(0,"", $aCall[0])

Thanks a lot mikell,

by the way, do you know if it is any way for the function to return the timestamp in the format dd/mm/aa hh:mm:ss? or some black magic with StringRegExp!

[jcpetu]

9 hours ago, TheXman said:

That GMT epoch value for "2019-10-06 8:59:03 p.m." is 1571259543 .  I have no idea where you got your value.

Hi TheXman, I wasn't sure it was the timestamp, that's the reason I wanted to convert it to readable.

[Musashi]

59 minutes ago, jcpetu said:

do you know if it is any way for the function to return the timestamp in the format dd/mm/aa hh:mm:ss?

Maybe this :

#include <Date.au3>
Local $iTimeStamp = 1571259543
Local $sDate  = _DateAdd('s', $iTimeStamp, '1970/01/01 00:00:00')
Local $sDate2 = StringRegExpReplace($sDate, "(\d{4})/(\d{2})/(\d{2}) (\d{2}):(\d{2}):(\d{2})", "${3}/${2}/${1} ${4}:${5}:${6}")
ConsoleWrite("> EpochToDate = " & $sDate & @CRLF)
ConsoleWrite("> Dateformat2 = " & $sDate2 & @CRLF)

[jcpetu]

1 hour ago, Musashi said:

Maybe this :

#include <Date.au3>
Local $iTimeStamp = 1571259543
Local $sDate  = _DateAdd('s', $iTimeStamp, '1970/01/01 00:00:00')
Local $sDate2 = StringRegExpReplace($sDate, "(\d{4})/(\d{2})/(\d{2}) (\d{2}):(\d{2}):(\d{2})", "${3}/${2}/${1} ${4}:${5}:${6}")
ConsoleWrite("> EpochToDate = " & $sDate & @CRLF)
ConsoleWrite("> Dateformat2 = " & $sDate2 & @CRLF)

Hi Musashi, yes it does it!

Thanks a lot.

[jchd]

For the record, SQLite doesn't propose any timestamp specific format.  You can store dates as text (e.g. '2019-12-09 17:32:17'), as integer (e.g. Un*x epoch), as Julian date, as Julian day, whatever.  You have to be consistent: reading back timestamps under the format you used when storing them.

[jcpetu]

Hi people, Im sorry to bother but the example works well for 10 digits timestamp but Chrome's timestamp is formatted as the number of microseconds since January, 1601 so I need to convert to seconds, calculate seconds until 01/01/1601 and  finally add the decimal part of EPOCH.

I do all calculations but the timestamp doesn't match with original timestamp, so if anyone could help please I will appreciate.

#include <array.au3>
#include <Constants.au3>
#include <Date.au3>

;            13218946054701800 = 22/11/2019 22:27:34
$EPOCHtime = 13218946054701800 / 1000000 ;------------------ convert from microseconds to seconds ---------------------
ConsoleWrite("$EPOCHtime = "&$EPOCHtime &@CRLF)

$time1 = _DateAdd('s', Int($EPOCHtime), "1601/01/01 00:00:00"); Chrome time base, 1601
ConsoleWrite("$time1 = " & $time1 &  @CRLF)

$decimalPart=$EPOCHtime -Int($EPOCHtime)
ConsoleWrite("$decimalPart= "&$decimalPart&@CRLF)

$time2 = _DateAdd('s',Int($decimalPart), $time1 )
ConsoleWrite("$time2 = " & $time2 &  @CRLF)

--> output
$EPOCHtime = 13218946054.7018
$time1 = 2019/11/23 01:27:34
$decimalPart= 0.701799392700195
$time2 = 2019/11/23 01:27:34
 

[Zedna]

Look at sources of _DateAdd() in Date.au3 include and add ConsoleWrite also there to see what's exactly happening ...

[KaFu]

Chrome time is based on UTC, add / subtract the offset to your own timezone.

Edit - This works fine for me:

#include <array.au3>
#include <Constants.au3>
#include <Date.au3>

;            13218946054701800 = 22/11/2019 22:27:34
$EPOCHtime = 13220446295000000 / 1000000 ;------------------ convert from microseconds to seconds ---------------------

$a_TimeZoneInfo = _Date_Time_GetTimeZoneInformation()
$EPOCHtime -= $a_TimeZoneInfo[1]*60

ConsoleWrite("$EPOCHtime = "&$EPOCHtime &@CRLF)

$time1 = _DateAdd('s', Int($EPOCHtime), "1601/01/01 00:00:00"); Chrome time base, 1601
ConsoleWrite("$time1 = " & $time1 &  @CRLF)

$decimalPart=$EPOCHtime -Int($EPOCHtime)
ConsoleWrite("$decimalPart= "&$decimalPart&@CRLF)

$time2 = _DateAdd('s',Int($decimalPart), $time1 )
ConsoleWrite("$time2 = " & $time2 &  @CRLF)

 

Edited December 10, 2019 by KaFu

[jcpetu]

KaFu, yes it works now, thanks a lot.

[jchd]

Since the topic was also on SQLite, you can use SQLite to perform the conversion for you, without using application code:

select datetime(13218946054701800 / 1000000 + strftime('%s', '1601-01-01'), 'unixepoch', 'localtime') as Converted;
outputs:
2019-11-23 02:27:34

We clearly don't have the same localtime than OP (here = Paris).

Better yet, use an SQLite trigger to perform once for all the conversion when the Chrome epoch data is entered. E.g.

CREATE TABLE [T](
  [Date] CHAR;

CREATE TRIGGER [trInsChromeEpoch]
AFTER INSERT ON [T]
FOR EACH ROW
WHEN CAST([new].[Date] AS INT) > 9999
BEGIN
  UPDATE
    [T]
  SET
    [Date] = DATETIME ([new].[Date] / 1000000 + STRFTIME ('%s', '1601-01-01'), 'unixepoch', 'localtime')
  WHERE
    [rowid] = [new].[rowid];
END;

INSERT INTO [T] VALUES(13218946054701800);

 

Edited December 10, 2019 by jchd

[jcpetu]

jchd,

yes this is an interesting option.

And if want to read a filled field with the EPOCH number 13 218946054701800, how do you do to convert?

[jchd]

A trigger like the one examplified above would do.

If you already have a table populated with Chrome epoch column(s), you just have to perform a massive UPDATE.

Use a 3rd-party SQLite manager (I warmly recommend SQLite Expert), run the above SQL snippet and watch the result being what you expect.  Due to our distinct timezones, you won't have the same output as Kafu or myself, but it should make you happy.

[jcpetu]

OK, I'll test it.

Thanks a lot jchd.

[jcpetu]

Hi, now trying to convert a timestamp to Chrome/webkit I can' t achieve it. So if somebody can help I'll appreciate. 

#include <Date.au3>

Local $Date = "2020/07/27 16:22:23" ;=13240351343870418
ConsoleWrite("Timestamp to Chrome/webkit = " & HumanToWebkit($Date) & @CRLF) ;OK

Func HumanToWebkit($Date)
    Local $WebKit = _DateAdd('s', -Int($Date), "1601/01/01 00:00:00")
    Local $a_TimeZoneInfo = _Date_Time_GetTimeZoneInformation()
    $Seconds = $a_TimeZoneInfo[1] * 60
    Local $DateWebKit = ($WebKit * ($Seconds + ($WebKit * 1000000)))
    Return StringFormat("%017i", $DateWebKit)
EndFunc   ;==>HumanToWebkit

 

[Nine]

Maybe this :

Local $Date = "2020/07/27 16:22:23" ;=13240351343870418
ConsoleWrite("Timestamp to Chrome/webkit = " & HumanToWebkit($Date) & @CRLF) ;OK

Func HumanToWebkit($Date)
    Local $WebKit = _DateDiff('s', "1601/01/01 00:00:00", $Date)
    Local $a_TimeZoneInfo = _Date_Time_GetTimeZoneInformation()
    $Seconds = $a_TimeZoneInfo[1] * 60
    Local $DateWebKit = $WebKit + $Seconds
    Return Int($DateWebKit & "000000")
EndFunc   ;==>HumanToWebkit

 

[jcpetu]

Nine, yes this is OK. 

Thanks a lot.