2

I just downloaded a csv from sqlite with a column containing dates. The df looks like this:

ROWID   text    is_from_me  handle_id   date
8       Esta muy    0       4           542735636047925696
9       Congratz    0       4           542735642840117952
10      En la       0       4           542735660785415872
11      Liked       1       4           542735693412165376

How do I turn the date column into readable dates?

Guillermina
  • 3,127
  • 3
  • 15
  • 24
  • Are those numbers real examples? They look too large to be timestamps. – Bemmu Dec 19 '18 at 02:42
  • Yes @Bemmu , they are real examples. I downloaded texts from iMessage (iOS) and that was the timestamp that came with it. – Guillermina Dec 19 '18 at 02:44
  • Can you find out what they represent? E.g. are they milliseconds since epoch? – user9074332 Dec 19 '18 at 02:45
  • @user9074332 this is what I've found here: https://stackoverflow.com/questions/1832714/18-digit-timestamp Don't know how to do it in Python tho... – Guillermina Dec 19 '18 at 02:55
  • Even for milliseconds since epoch they are too big. For example 542735636047925696/1000 is still in the year 17200577. – Bemmu Dec 19 '18 at 03:01
  • Maybe the date/time was stored in a ```long int``` representation of a TAI64 date format. – accdias Dec 19 '18 at 03:02
  • More info here: https://cr.yp.to/libtai/tai64.html – accdias Dec 19 '18 at 03:03
  • I tried decoding one using tai64n library, but t.decode_tai64n(hex(542735636047925696)) would have resulted in some year before epoch. – Bemmu Dec 19 '18 at 03:07
  • Looks like they are expressed in nanoseconds. Here is what I got from ```datetime.datetime.fromtimestamp(542735636047925696/1000000000)```: ```datetime.datetime(1987, 3, 14, 12, 53, 56, 47926)``` – accdias Dec 19 '18 at 03:38

2 Answers2

3

It seems the first 9 digits may represent seconds since 2001-01-01 00:00:00.

import time, datetime
t = time.gmtime(int("542735636047925696"[0:9])+978307200)
timestamp = time.mktime(t)
dt = datetime.datetime.fromtimestamp(timestamp)    
print(dt.strftime('%Y-%m-%d %H:%M:%S'))

# 2018-03-14 15:53:56

Based on Parsing "date" field of iPhone SMS file from backup

Bemmu
  • 17,849
  • 16
  • 76
  • 93
1

Based on the accepted answer, I adjusted mine just to have another way registered here. :-)

from datetime import datetime

# 1970-01-01 00:00:00
unix_epoch = datetime(1970, 1, 1, 0, 0, 0)

# 2001-01-01 00:00:00   
mac_epoch = datetime(2001, 1, 1, 0, 0, 0)

# 1s = 10e9 ns
ns = 10**9

date = datetime.fromtimestamp(542735636047925696/ns) + (mac_epoch - unix_epoch)

print(date)

>>> 2018-03-14 15:53:56.047926
accdias
  • 5,160
  • 3
  • 19
  • 31