Decipher data to date

[HurleyShanabarger]

Hello,

I am using an ADOB connection to dump source files from a project database which is controlled by an third-party application. This is working fine, but I want to get the save time from the source files that are being dumped. The issue with that is, that the date is stored in a format I am not able to under/convert:

#     Timestamp              User Interface                         Date formatted         Excel         Unix
1     30985482 2725020241    Tuesday, 20 September 2022 18:04:16    20/09/2022 18:04:16    44824,7530    1663697056
2     30985483 539393273     Tuesday, 20 September 2022 18:07:47    20/09/2022 18:07:47    44824,7554    1663697267
3     30985484 2313755652    Tuesday, 20 September 2022 18:17:54    20/09/2022 18:17:54    44824,7624    1663697874
4     30985485 1156667179    Tuesday, 20 September 2022 18:23:08    20/09/2022 18:23:08    44824,7661    1663698188
5     30985485 1156587657    Tuesday, 20 September 2022 18:23:08    20/09/2022 18:23:08    44824,7661    1663698188
6     30985485 1324352071    Tuesday, 20 September 2022 18:23:08    20/09/2022 18:23:08    44824,7661    1663698188
7     30985485 2239011360    Tuesday, 20 September 2022 18:24:56    20/09/2022 18:24:56    44824,7673    1663698296
8     30985482 2313755652    Tuesday, 20 September 2022 18:03:35    20/09/2022 18:03:35    44824,7525    1663697015
9     30985480 2313755652    Tuesday, 20 September 2022 17:49:16    20/09/2022 17:49:16    44824,7425    1663696156
10    30985481 2313755652    Tuesday, 20 September 2022 17:56:25    20/09/2022 17:56:25    44824,7475    1663696585
11    30985483 2313755652    Tuesday, 20 September 2022 18:10:44    20/09/2022 18:10:44    44824,7575    1663697444
12    30985484 2313755652    Tuesday, 20 September 2022 18:17:54    20/09/2022 18:17:54    44824,7624    1663697874
13    30985485 2313755652    Tuesday, 20 September 2022 18:25:03    20/09/2022 18:25:03    44824,7674    1663698303
14    30985486 2313755652    Tuesday, 20 September 2022 18:32:13    20/09/2022 18:32:13    44824,7724    1663698733

Colums "Timestamp" is date from the database, column "User Interface" is the date shown if the user is opening a dialog within the application. The other columns I created/added for understand the format, but I am not getting anywhere with that.

Rows 1-7 are from normal working with the database, Rows 8-14 I manually edited in the database and got the result as shown in the UserInterface

Does someone have an idea, how i can decipher the timestamp?

Edited September 20, 2022 by HurleyShanabarger

[Musashi]

Unfortunately I have not found anything regarding the first timestamp (there are so many formats ). Could you please post the name of the application / database.

2 hours ago, HurleyShanabarger said:

The other columns I created/added for understand the format, but I am not getting anywhere with that.

The conversion of a Unix timestamp to a date/time notation would look like this for example. I don't know if this helps you in somehow.

; complete example by @Realm
; https://www.autoitscript.com/forum/topic/153617-seconds-since-epoch-aka-unix-timestamp/

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

Local $iUnixTime = 1663697056
Local $sUnixDateTime = _GetDateTime_FromUnixTime($iUnixTime)
MsgBox($MB_SYSTEMMODAL, "Unix Timestamp", "Get Date from Unix Timestamp in Local Time" & @CRLF & _
                                          "UnixTime = " & $iUnixTime & @CRLF & _
                                          "DateTime = " & $sUnixDateTime & @CRLF)

Func _GetDateTime_FromUnixTime($iUnixTime, $iReturnLocal = True)
    Local $aRet = 0, $aDate = 0
    Local $aMonthNumberAbbrev[13] = ["", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    Local $timeAdj = 0
    If Not $iReturnLocal Then
        Local $aSysTimeInfo = _Date_Time_GetTimeZoneInformation()
        Local $timeAdj = $aSysTimeInfo[1] * 60
        If $aSysTimeInfo[0] = 2 Then $timeAdj += $aSysTimeInfo[7] * 60
    EndIf

    $aRet = DllCall("msvcrt.dll", "str:cdecl", "ctime", "int*", $iUnixTime + $timeAdj )

    If @error Or Not $aRet[0] Then Return ""
    $aDate = StringSplit(StringTrimRight($aRet[0], 1), " ", 2)
    Return $aDate[4] & "/" & StringFormat("%.2d", _ArraySearch($aMonthNumberAbbrev, $aDate[1])) & "/" & $aDate[2] & " " & $aDate[3]
EndFunc   ;==>_GetDateTime_FromUnixTime

 

[HurleyShanabarger]

Hi @Musashi,

thank you for your support, it helped me to look in the right place (_Date_Time_* UDF).

The format I encountered is the FILETIME format from Microsoft and that can easily converted with all the wonderful UDF from AutoIt ♥