Complex unknown date / time representation

Question

Log files containing date / time in a strange format make me awake.
I did not maneuver to reverse engineer the algorithm used. Using DCode with www.digital-detective.co.uk did not work either. What I know: this is a 64-bit value, and it starts on January 1, 1900. Some examples:

Date / Time: Hexadecimal:
    1900-01-01 00:00:00> 0000000000000000
    2006-07-19 00:00:00> 0000000000A000E3
    2008-04-14 00:00:00> 00000000000050E3
    2008-04-15 11: 04:32> 00D6CF74C42E50E3
    2008-04-15 11:04:46> 00CEA0C8C52E50E3
    2008-04-15 11:08:32> 00EC3B36DB2E50E3
    2008-04-16 11:08:43> 008B3B41DC4E50E3
    2008-04-16 11:21: 02> 00B3AD52224F50E3
    2012-02-21 00:00:00> 00000000000000E4
    2012-03-13 13:37:54> 007A35F12CB202E4
    2012-10-22 16:27:13> 001F7A2AF0951EE4

Any idea how to convert the setpoint to a date / time to restore sleep?

Answer 1

, . , EPOCH, E3 E4, . , 00:00:00 , .

, , , , , - . . , , , , , . , , , .

: -, . , E3 → F3, E3 → E4. DOH.

100 , 1000 10000 EPOCH. .

Answer 2

, , . , :

• Byte-swap: , endian
• , Int.
• Timestamp = A*Int + B, A = 2.45563569478691e-09 B = -39014179200.000061
• Timestamp ( 1970-01-01) Date.

, :

              Datetime               Hex              RHex              Flip  \
0  1900-01-01 00:00:00  0000000000000000  0000000000000000  0000000000000000   
1  2006-07-19 00:00:00  0000000000A000E3  3E000A0000000000  E300A00000000000   
2  2008-04-14 00:00:00  00000000000050E3  3E05000000000000  E350000000000000   
3  2008-04-15 11:04:32  00D6CF74C42E50E3  3E05E24C47FC6D00  E3502EC474CFD600   
4  2008-04-15 11:04:46  00CEA0C8C52E50E3  3E05E25C8C0AEC00  E3502EC5C8A0CE00   
5  2008-04-15 11:08:32  00EC3B36DB2E50E3  3E05E2BD63B3CE00  E3502EDB363BEC00   
6  2008-04-16 11:08:43  008B3B41DC4E50E3  3E05E4CD14B3B800  E3504EDC413B8B00   
7  2008-04-16 11:21:02  00B3AD52224F50E3  3E05F42225DA3B00  E3504F2252ADB300   
8  2012-02-21 00:00:00  00000000000000E4  4E00000000000000  E400000000000000   
9  2012-03-13 13:37:54  007A35F12CB202E4  4E202BC21F53A700  E402B22CF1357A00   
10 2012-10-22 16:27:13  001F7A2AF0951EE4  4EE1590FA2A7F100  E41E95F02A7A1F00   

                     Int    Timestamp                 Date  
0                      0 -39014179200  0733-09-10 00:00:00  
1   16357249768470085632   1153267200  2006-07-19 00:00:00  
2   16379591844746493952   1208131200  2008-04-14 00:00:00  
3   16379643266054739456   1208257472  2008-04-15 11:04:32  
4   16379643271755910656   1208257486  2008-04-15 11:04:46  
5   16379643363789106176   1208257712  2008-04-15 11:08:32  
6   16379678552640686848   1208344123  2008-04-16 11:08:43  
7   16379678853581091584   1208344862  2008-04-16 11:21:02  
8   16429131440647569408   1329782400  2012-02-21 00:00:00  
9   16429890296696109568   1331645874  2012-03-13 13:37:54  
10  16437740548686225152   1350923233  2012-10-22 16:27:13  

, () Date () Datetime , 1900-01-01 00:00:00. , MySQL 1900-01-01 00:00:00 .

, A B . Datetime s, Timestamp. A B , Timestamp Int s.

python, , :

import pandas as pd
import numpy as np
import struct
import binascii

df = pd.read_table('data', sep='\s{2,}', parse_dates=[0])

df['RHex'] = df['Hex'].str[::-1]

def flip_endian(x):
    xbytes = binascii.unhexlify(x)
    swapped = struct.pack('<8h', *struct.unpack('>8h', x))
    return swapped
df['Flip'] = df['RHex'].apply(flip_endian)

df['Int'] = df['Flip'].apply(lambda x: int(x, 16))

# The constants were found by linear regression:
# import scipy.stats as stats
# df['Timestamp'] = (df['Datetime'] - pd.Timestamp('1970-1-1')).dt.total_seconds()
# A, B, rval, pval, stderr = stats.linregress(df['Int'], df['Timestamp'])
A, B = 2.45563569478691e-09, -39014179200.000061

df['Timestamp'] = (A*df['Int'] + B).astype(int)
df['Date'] = (np.array(['1970-01-01'], dtype='datetime64[D]') 
              + np.array(df['Timestamp'], dtype='timedelta64[s]')).tolist()

print(df)