Kitz ADSL Broadband Information
adsl spacer  
Support this site
Home Broadband ISPs Tech Routers Wiki Forum
 
     
   Compare ISP   Rate your ISP
   Glossary   Glossary
 
Please login or register.

Login with username, password and session length
Advanced search  

News:

Author Topic: Dates change themselves to weird number when exported to CSV  (Read 256 times)

renluop

  • Kitizen
  • ****
  • Posts: 3022
Dates change themselves to weird number when exported to CSV
« on: October 09, 2018, 11:06:29 AM »

I was connected to a site (not Companies House) that supplies financial data of companies. The accounts' dates are such as 30 Jul 2017 and the data can be exported in CSV.
When, however, the CSV file is opened the date appears as a large number, in this case 1501372800. 30/07/2017's value in Excel is 42946, and doesn't seem to have any obvious relationship to 1501372800.

Knowing the quality of brains here, I'm wondering if any one has any idea why 30 Jul 2017 becomes such a weird number in CSV, and secondly how to convert that to a recognisable date.
Logged

broadstairs

  • Kitizen
  • ****
  • Posts: 3100
Re: Dates change themselves to weird number when exported to CSV
« Reply #1 on: October 09, 2018, 11:14:33 AM »

My only thought - what is the property value of the cell? I get funny values showing if not defined as a date value.

Stuart
Logged
ISP:TalkTalk Connection:FTTC Cab:ECI Router:Netgear D6220

watcher

  • Reg Member
  • ***
  • Posts: 144
Re: Dates change themselves to weird number when exported to CSV
« Reply #2 on: October 09, 2018, 11:22:33 AM »

The 10 digit number would suggest that this is a Unix date format based on the number of seconds that have elapsed since January 1 1970 (yes, really!).

The solution is to import the CSV file into Excel, add an additional column and apply the following formula to each of these numbers (this is with 1501372800 in A1) :

=FLOOR(A1/60/60/24,1) + DATE(1970,1,1)

and the result in Excel is 30/07/2017
Logged

renluop

  • Kitizen
  • ****
  • Posts: 3022
Re: Dates change themselves to weird number when exported to CSV
« Reply #3 on: October 09, 2018, 03:57:55 PM »

Wow! Thanks, watcher! Why on earth did the site use something that would produce such an oddity in Excel has me confused.
Logged

Westie

  • Reg Member
  • ***
  • Posts: 997
Re: Dates change themselves to weird number when exported to CSV
« Reply #4 on: October 09, 2018, 05:21:26 PM »

Someone else asked that at https://stackoverflow.com/questions/2533563/why-are-dates-calculated-from-january-1st-1970

This was the answer:

Quote
Unix time, or POSIX time, is a system for describing points in time, defined as the number of seconds elapsed since midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds.
Logged

watcher

  • Reg Member
  • ***
  • Posts: 144
Re: Dates change themselves to weird number when exported to CSV
« Reply #5 on: October 09, 2018, 09:15:45 PM »

The advantage of storing the date in the 10 digit Unix format is that it provides a consistent base from which a date formatted to suit a particular locale can be calculated. Storing dates in a UK format could lead to issues. For example today's date, 9th October, is 09/10/2018 in the UK; in the USA this would read as September 10 2018. The Unix number also allows dates to be sorted, and calculations made on dates without the complication of dealing with date formats.
Logged

jelv

  • Helpful
  • Kitizen
  • *
  • Posts: 1100
Re: Dates change themselves to weird number when exported to CSV
« Reply #6 on: October 09, 2018, 09:36:39 PM »

How many people downloading this data will be using Excel and how many will be using Unix?

Excel has a perfectly good way of storing dates as numbers of days since January 1, 1900 (with option of time as fraction of a day). This meets the requirement of allowing dates to be formatted to a particular locale.

Unix starting at January 1, 1970 is bonkers. How are dates of birth before that date stored?
Logged
Line rental: Pulse8, Broadband: AAISP Home::1 FTTC 80/20, Mobile: id Mobile

DaveC

  • Member
  • **
  • Posts: 76
Re: Dates change themselves to weird number when exported to CSV
« Reply #7 on: October 09, 2018, 09:47:13 PM »

This is clearly just a bug in the data extract routine on the website - the Unix timestamp is just an internal format, and shouldn't be output that way in general-purpose CSV files.  Similarly, I wouldn't expect CSV files to contain the internal Excel representation.

Most compatible would be the YYYY-MM-DD format.

So if I was the original poster, I would contact the website and let them know about it.  I very much doubt it was a deliberate choice.
Logged

DaveC

  • Member
  • **
  • Posts: 76
Re: Dates change themselves to weird number when exported to CSV
« Reply #8 on: October 09, 2018, 09:54:29 PM »

Unix starting at January 1, 1970 is bonkers. How are dates of birth before that date stored?

Negative numbers  ;D  IIUC, in modern Unixes, time is a 64-bit signed integer, so can store dates/times waaaay into the past and future.
Logged

jelv

  • Helpful
  • Kitizen
  • *
  • Posts: 1100
Re: Dates change themselves to weird number when exported to CSV
« Reply #9 on: October 10, 2018, 12:15:44 AM »

This is clearly just a bug in the data extract routine on the website - the Unix timestamp is just an internal format, and shouldn't be output that way in general-purpose CSV files.  Similarly, I wouldn't expect CSV files to contain the internal Excel representation.

Most compatible would be the YYYY-MM-DD format.

Totally agree. Given that the extract relates to financial data of companies, a standard used mainly by computer geeks is probably the absolute worst option followed by a proprietary format (Excel); ISO 8601 format would be totally unambiguous.

I really do hope it was an oversight and not a deliberate choice!
Logged
Line rental: Pulse8, Broadband: AAISP Home::1 FTTC 80/20, Mobile: id Mobile