17

How can one convert a serial date number, representing the number of days since epoch (1970), to the corresponding date string? I have seen multiple posts showing how to go from string to date number, but I haven't been able to find any posts on how to do the reverse.

For example, 15951 corresponds to "2013-09-02".

>>> import datetime
>>> (datetime.datetime(2013, 9, 2) - datetime.datetime(1970,1,1)).days + 1
15951

(The + 1 because whatever generated these date numbers followed the convention that Jan 1, 1970 = 1.)

TL;DR: Looking for something to do the following:

>>> serial_date_to_string(15951)  # arg is number of days since 1970
"2013-09-02"

This is different from Python: Converting Epoch time into the datetime because I am starting with days since 1970. I not sure if you can just multiply by 86,400 due to leap seconds, etc.

Community
  • 1
  • 1
pault
  • 41,343
  • 15
  • 107
  • 149
  • I'm also open to bash shell script solutions. – pault Oct 11 '16 at 20:22
  • Possible duplicate of [Python: Converting Epoch time into the datetime](http://stackoverflow.com/questions/12400256/python-converting-epoch-time-into-the-datetime) – Cavaz Oct 11 '16 at 20:24
  • 6
    Use: `datetime.datetime(1970, 1, 1, 0, 0) + datetime.timedelta(15951)` – sisanared Oct 11 '16 at 20:25
  • thanks @SSNR - this works: `datetime.datetime(1970,1,1) + datetime.timedelta(15951 - 1)` – pault Oct 11 '16 at 20:29
  • 2
    you can also use `epoch = datetime.datetime.utcfromtimestamp(0)` to get the epoch datetime, instead of hardcoding. then follow @SSNR – harishankarv Oct 11 '16 at 20:46

2 Answers2

25

Use the datetime package as follows:

import datetime
def serial_date_to_string(srl_no):
    new_date = datetime.datetime(1970,1,1,0,0) + datetime.timedelta(srl_no - 1)
    return new_date.strftime("%Y-%m-%d")

This is a function which returns the string as required.

So:

serial_date_to_string(15951)

Returns

>> "2013-09-02"
AER
  • 1,549
  • 19
  • 37
  • 8
    Be careful with the -1. This answer, like the question, assumes that day 1 will be 1970-01-01 and day 0 will be 1969-12-31. If you want 1970-01-01 to be day 0, remove the -1 from the function. – Zonyl Jul 07 '20 at 14:15
  • Good comment @Zonyl. Using Javas `LocalDate` (`LocalDate.ofEpochDay(15951)`) will also return `2013-09-03`, this is what you probably want. – Tobias Feb 09 '22 at 16:19
  • I spent 4 hours in datetime purgatory because pandas datetimeindex and matplotlib do not play nice when showing xticks. This answer was a good work around. – Finncent Price Jun 14 '23 at 17:06
5

And for a Pandas Dataframe:

df["date"] = pd.to_datetime(df["date"], unit="d")

... assuming that the "date" column contains values like 18687 which is days from Unix Epoch of 1970-01-01 to 2021-03-01.

Also handles seconds and milliseconds since Unix Epoch, use unit="s" and unit="ms" respectively.

Also see my other answer with the exact reverse.

Contango
  • 76,540
  • 58
  • 260
  • 305