Convert JSON timestamp string into python date in pandas dataframe

Question

I have a pandas dataframe which I have read from JSON, one date column is a strange timestamp format like follows

"/Date(1405961743000+0100)/"

. How can I convert the entire column into a python date?

I have been able to manually convert that date into a python date by using the datetime fromtimestamp function on the first 10 digits i.e. datetime.datetime.fromtimestamp(1405961743) but am struggling to convert the whole column.

I am guessing I need to select the appropriate digits from each entry, convert to integer, then use the fromtimestamp function, but I am new to python (and pandas) so am struggling to do this.

Any help would be appreciated.

Thanks

Answer 1

Obviously it would be better if you know where the JSON comes from, and can look in the docs/ask the author/etc. to know what the actual intention is behind that date format. (It might even be generated by Python code, using a library you can just use yourself…)

But looking at the numbers, I can make a pretty good guess what this means: the 1405961743000 is milliseconds since the Unix epoch (which explains why you can use the first 10 digits of it as seconds since the Unix epoch, at least within a pretty wide range around 2014), and the +0100 is a timezone offset from GMT, in +HHMM format.

So, instead of extracting the first 10 digits, converting to int, and calling fromtimestamp, you'd want to extract everything up to the + or -, convert to int, divide by 1000, and call fromtimestamp. Although the fact that the only example you've given us happens to have 0 milliseconds implies that there's a good chance they all will, in which case this difference won't matter…

Anyway, it's then up to you what to do with the timezone offset. Do you want to store aware local datetimes? GMT datetimes? naive local datetimes? They're all pretty easy to get to from a timestamp and an offset (although "aware" is going to mean using a fake timezone like GMT-05:00, which doesn't have any historical or DST information, of course), but you have to decide which one you want.

---

Whatever you end up doing, you may want to consider extending your JSON decoder to automate it, as shown in the examples in the docs. (Any string that matches the regex r'/Date\((\d+)([+-]\d{4})\)/', the first group is the timestamp and the second the offset.)

But maybe not. Especially since parse_string doesn't seem to be overridable, at least as of 3.4, so it looks like you'd have to monkeypatch it. See this code I slapped together as a proof of concept; you might be able to make it a little nicer, but there's a limit to how clean you can make it if they didn't provide a hook for it…

---

PS, if you're ever extending JSON yourself, you may want to consider a more standardized and self-documenting way of doing this. The dict format shown in the json module docs, where you effectively specify a constructor to call and the arguments to pass it, is a lot easier for people to figure out (and to add a hook for). Or, alternatively, there's a quasi-standard way of encoding YAML formats as JSON formats, and YAML is extensible (and already has a standard timestamp extension).

Answer 2

The time string is OData version 2 JSON verbose format for Datetime:

“/Date(<ticks>[“+” | “-” <offset>])/”
<ticks> = number of milliseconds since midnight Jan 1, 1970
<offset> = number of minutes to add or subtract

As @Matt Johnson mentions the format may be seen in ASP.NET or WCF applications.

#!/usr/bin/env python3
import re
from datetime import datetime, timedelta, timezone

time_string = "/Date(1405961743000+0100)/"
epoch = datetime(1970, 1, 1, tzinfo=timezone.utc)
ticks, offset = re.match(r'/Date\((\d+)([+-]\d{4})?\)/$', time_string).groups()
utc_dt = epoch + timedelta(milliseconds=int(ticks))
print(utc_dt, utc_dt.strftime('%Z'))
if offset:
   offset = int(offset)
   # http://www.odata.org/documentation/odata-version-2-0/json-format
   # says offset is minutes (an error?)
   dt = utc_dt.astimezone(timezone(timedelta(minutes=offset)))
   print(dt, dt.strftime('%Z'))
   # but it looks like it could be HHMM
   hours, minutes = divmod(abs(offset), 100)
   if offset < 0:
      hours, minutes = -hours, -minutes
   dt = utc_dt.astimezone(timezone(timedelta(hours=hours, minutes=minutes)))
   print(dt, dt.strftime('%Z'))

Output

2014-07-21 16:55:43+00:00 UTC+00:00
2014-07-21 18:35:43+01:40 UTC+01:40
2014-07-21 17:55:43+01:00 UTC+01:00

It looks like the odata.org docs should be ignored and the offset should be treated as HHMM format.

Answer 3

I recently had to solve the same problem. Fortunately, I found this post and managed to adapt the solution to format all the dates in a JSON file, coming from an OData-based API of MS Project Online. Thank you very much, @abarnert and @jfs! Here's the code I used:

    from datetime import datetime, timedelta, timezone
    import re


    def ms_to_timestamp(
        milliseconds: str, 
        offset: str, 
        date_format: str = "%Y-%m-%d %H:%M:%S"
    ) -> str:
        epoch = datetime(1970, 1, 1, tzinfo=timezone.utc)
        utc_dt = epoch + timedelta(milliseconds=int(milliseconds))
        timestamp = utc_dt.strftime(date_format)

        if offset:
            offset = int(offset)
            hours, minutes = divmod(abs(offset), 100)

            if offset < 0:
                hours, minutes = -hours, -minutes

            dt = utc_dt.astimezone(timezone(timedelta(hours=hours, minutes=minutes)))
            timestamp = dt.strftime(date_format)

        return timestamp


    def format_datetime(string: str) -> str:
        dates = re.findall(r'Date\((\d+)([+-]\d{4})?\)', string)

        for d in dates:
            timestamp = ms_to_timestamp(milliseconds=d[0], offset=d[1])
            string = string.replace(f"/Date({d[0]}{d[1]})/", timestamp)

        return string


    if __name__ == '__main__':
        text = "example 1: /Date(1615885200000)/ ; example 2: /Date(1405961743000+0100)/"
        new_text = format_datetime(text)
        print(f"Before: {text}\nAfter: {new_text}")

    # Output
    # ------
    # Before: example 1: /Date(1615885200000)/ ; example 2: /Date(1405961743000+0100)/
    # After: example 1: 2021-03-16 09:00:00 ; example 2: 2014-07-21 17:55:43