1

I have an excel file which contains date format in 10 digit.

For example, Order Date as 1806825282.731065, Purchase Date as 1806765295

Does anyone know how to convert them to a proper date format such as dd/mm/yyyy hh:mm or dd/mm/yyyy? Any date format will be fine.

I tried pd.to_datetime but does not work.

Thanks!

user13617491
  • 87
  • 6

4 Answers4

3

You can do this

(pd.to_timedelta(1806825282, unit='s') + pd.to_datetime('1960-1-1'))

or

(pd.to_timedelta(df['Order Date'], unit='s') + pd.to_datetime('1960-1-1'))
Monir Saade Castillo
  • 66
  • 2
  • Just found out that you can use the `origin` argument of `to_datetime()`, so a clearer way to convert the date is to do `pd.to_datetime(1806825282, unit='s',origin='1960-1-1')` – Monir Saade Castillo Feb 08 '23 at 15:49
2

SAS timestamp are stored in seconds from 1960-1-1:

import pandas as pd

origin = pd.Timestamp('1960-1-1')
df = pd.DataFrame({'Order Date': [1806825282.731065],
                   'Purchase Date': [1806765295]})

df['Order Date'] = origin + pd.to_timedelta(df['Order Date'], unit='s')
df['Purchase Date'] = origin + pd.to_timedelta(df['Purchase Date'], unit='s')

Output:

>>> df
                     Order Date       Purchase Date
0 2017-04-03 07:54:42.731065035 2017-04-02 15:14:55

From The Essential Guide to SAS Dates and Times

SAS has three separate counters that keep track of dates and times. The date counter started at zero on January 1, 1960. Any day before 1/1/1960 is a negative number, and any day after that is a positive number. Every day at midnight, the date counter is increased by one. The time counter runs from zero (at midnight) to 86,399.9999, when it resets to zero. The last counter is the datetime counter. This is the number of seconds since midnight, January 1, 1960. Why January 1, 1960? One story has it that the founders of SAS wanted to use the approximate birth date of the IBM 370 system, and they chose January 1, 1960 as an easy- to-remember approximation.

Corralien
  • 109,409
  • 8
  • 28
  • 52
1

You can use something like this:

# Convert the 10-digit datetime to a datetime object
df['date_column'] = pd.to_datetime(df['date_column'], unit='s')

# Format the datetime object to the desired format
df['date_column'] = df['date_column'].dt.strftime('%d/%m/%Y %H:%M')

Or if you want a one-liner:

df['date_column'] = pd.to_datetime(df['date_column'], unit='s').dt.strftime('%d/%m/%Y %H:%M')
Lahcen YAMOUN
  • 657
  • 3
  • 15
1

According to The Pandas Documentation Link: https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html

Code

>>> pd.to_datetime(1674518400, unit='s')
Timestamp('2023-01-24 15:16:45')

>>> pd.to_datetime(1674518400433502912, unit='ns')
Timestamp('2023-01-24 15:16:45.433502912')

# you can use template
df[DATE_FIELD]=(pd.to_datetime(df[DATE_FIELD],unit='ms'))
Mykola Hryshchenko
  • 11
  • 2