2

I have a set of 19 digit timestamps, stamp, that I cannot figure out how to convert to a datetime format.

  • For comparison, stamp corresponds to dt.
  • In the following code, dt has been converted to datetime and a unix timestamp.

I've read similar SO posts and it's not just a matter of dividing by 1,000,000,000. Also, it doesn't seem to be a double issue like from How to convert a really long timestamp into datetime (19 digits) (9876432101234567890).

Examples:

import pandas as pd

data = {'stamp': [1264604283246383104, 1266445459956158467, 1269744490329358337, 1270363071710715905],
        'dt': ['May 24 2020 13:08 EST', 'May 29 2020 15:05 EST', 'Jun 7 2020 17:34 EST', 'Jun 9 2020 10:32 EST']}

df = pd.DataFrame(data)

# move timezone to a separate column
df['tz'] = df['dt'].str[-4:]
df['dt'] = df['dt'].str.replace(' EST', '')

# convert dt to UTC datetime
df['datetime'] = pd.to_datetime(df['dt']).dt.tz_localize(tz='US/Eastern').dt.tz_convert('UTC')

# convert datetime to unix datetime
df['datetime_unix'] = df['datetime'].astype(int)

                 stamp                 dt    tz                  datetime        datetime_unix
0  1264604283246383104  May 24 2020 13:08   EST 2020-05-24 17:08:00+00:00  1590340080000000000
1  1266445459956158467  May 29 2020 15:05   EST 2020-05-29 19:05:00+00:00  1590779100000000000
2  1269744490329358337   Jun 7 2020 17:34   EST 2020-06-07 21:34:00+00:00  1591565640000000000
3  1270363071710715905   Jun 9 2020 10:32   EST 2020-06-09 14:32:00+00:00  1591713120000000000

Thoughts on what this is and how to convert via Python?

Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
jcgoette
  • 45
  • 1
  • 6

2 Answers2

4

That timestamp format is consistent but nonsensical. By doing (1270363071710715905 - 1264604283246383104)/(Jun 9 2020 10:32 EST - May 24 2020 13:08 EST), we determine that your timestamp ticks at a rate of approximately 4.2GHz. By then doing Jun 9 2020 10:32 EST - 1270363071710715905/4.2GHz, we determine that your timestamp's epoch was in early November 2010. I'm unaware of any common or well-known timestamp with these properties, but that information is enough for you to be able to convert arbitrary times to and from it.

Joseph Sible-Reinstate Monica
  • 45,431
  • 5
  • 48
  • 98
3

Joseph Sible-Reinstate Monica does an excellent job of explaining the steps to solving your problem. But the answer is missing specifics and code, so I thought I'd fill that in for you.

If there's a linear relationship between the timestamp numbers and date/time, it only takes two samples of each to establish the relationship. Having more as you do makes it possible to test the solution and be confident that the relationship really is linear.

>>> import datetime as dt
>>> date_list=[(1264604283246383104, dt.datetime(2020,5,24, 13,8)),
               (1266445459956158467, dt.datetime(2020,5,29, 15,5)),
               (1269744490329358337, dt.datetime(2020,6,7, 17,34)),
               (1270363071710715905, dt.datetime(2020,6,9, 10,32))]
>>> freq = (date_list[-1][0] - date_list[0][0]) / (date_list[-1][1] - date_list[0][1]).total_seconds()
>>> freq
4194188417.185807
>>> epoch = date_list[0][1] - dt.timedelta(seconds=date_list[0][0] / freq)
>>> epoch
datetime.datetime(2010, 11, 3, 19, 23, 34, 828853)
>>> def stamp_to_dt(stamp):
    return epoch + dt.timedelta(seconds=stamp/freq)

>>> for stamp, date in date_list:
    print(stamp, date, stamp_to_dt(stamp))

1264604283246383104 2020-05-24 13:08:00 2020-05-24 13:08:00
1266445459956158467 2020-05-29 15:05:00 2020-05-29 15:04:22.832110
1269744490329358337 2020-06-07 17:34:00 2020-06-07 17:33:54.641731
1270363071710715905 2020-06-09 10:32:00 2020-06-09 10:32:00

As you can see, the function output matches the expected result pretty closely. It matches the first and last exactly, because those were the two that were used to calculate the conversion factors.

Mark Ransom
  • 299,747
  • 42
  • 398
  • 622