Skip to main content
  • EN
Hi,

 

I have a SQL database which some date colums all of which are stored as int.

 

The dates are in Epoch format, ie the length of time since Jan 1st 1970. An example of a value in the column is 16261 which actually equates to 08/07/2014 and 15896 which equates to 09/07/2013. I have tried to use the date formater setting the source date format as %s and also have dowloaded the custom transformer date convertor, but can't see anyway in it to set the source as Epoch time. Any help would be much appreciated, but I do not know how to use phyton scripts.

 

Regards,

 

Helen 
Hi Helen,

 

 

Are you sure you have all the digits? to get a result of 08/072014 you need minimally 10 digits acoording to the converter.

 

 

Hope this helps

 

Itay
Hi Helen,

 

 

The epoch time is usually defined as the count of seconds since 00:00:00 of Jan. 1st 1970, but those numbers (16261 and 15896) seem to be the count of days.

 

 

Takashi
Hi,

 

Thanks for the replies. Yes I have all the digits. If I copy those 2 examples into an excel spreadsheet and then format them as date time they appear as below which is the correct day and month but the year is 70 years out. I was able to check what the correct date should be by asking a user to look in the application and check how the dates for a particular record appear in the system.So confused!!

 

Thanks,

 

Helen

 

Int Column Formatted as date time 16261 08/07/1944 15896 09/07/1943
Hi,

 

 

The date time value used internally in the Excel is NOT the standard epoch time, it's a kind of Microsoft specification. Make sure differences between them.

 

> Unix time (Wiki)

 

http://en.wikipedia.org/wiki/Unix_time

 

> How Excel stores date and time values (O'Reilly)

 

http://answers.oreilly.com/topic/1694-how-excel-stores-date-and-time-values/

 

 

Takashi
If those two examples represented the number of days since 1970-01-01, you can convert them into the number of seconds since 1970-01-01 00:00:00 (i.e. the epoch time) with multiplying by 86400 (= 24hr. * 60min. * 60sec.).

 

But the results were 16261 --> 2014-07-10, 15896 --> 2013-07-10 in my calculation. Are these dates wrong?

 

 

Takashi
I had a similar issue with Dublin Julian Dates. To solve this, create a table with the value and dates and join. So you will have a table with 0 = date, 1 = date +1 day, 2 = date + 2 days, etc... You can use excel to make the table then copy it to the database.

Reply


Community Stats

30,162
Posts
114,483
Replies
37,810
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.