Hacker News Reader

Comment by tyingq | Hacker News Reader

If you're thinking it's already largely resolved, this is from 64 bit mysql. I assume similar issues exist in other software.

  mysql> SELECT UNIX_TIMESTAMP('2037-11-13 10:20:19');
  +---------------------------------------+
  | UNIX_TIMESTAMP('2037-11-13 10:20:19') |
  +---------------------------------------+
  |                            2141742019 |
  +---------------------------------------+
  1 row in set (0.00 sec)

  mysql> SELECT UNIX_TIMESTAMP('2039-11-13 10:20:19');
  +---------------------------------------+
  | UNIX_TIMESTAMP('2039-11-13 10:20:19') |
  +---------------------------------------+
  |                                     0 |
  +---------------------------------------+

View on HN

See also "System call conversion for year 2038" (https://lwn.net/Articles/643234/) for a brief discussion of the Linux case.

Works in PostgreSQL 9.6.1

  postgres> SELECT extract(epoch from to_timestamp('2037-11-13 10:20:19', 'YYYY-MM-DD hh24:mi:ss'));
   date_part  
  ------------
   2141720419
  (1 row)


  postgres> SELECT extract(epoch from to_timestamp('2039-11-13 10:20:19', 'YYYY-MM-DD hh24:mi:ss'));
   date_part  
  ------------
   2204792419
  (1 row)

Well, we still have 21 years to solve it

A bit pedantic, I suppose, but Y2K problems showed up well ahead of Y2K. Things like credit cards with expiration dates well in the future, and validation code making comparisons.

Yes, our system doesn't work on some operating systems because we for some reason use dates after 2050 as a flag for something. And that breaks together if we testrun on windows but works fine on Linux.

Unless of course, you are trying to store the maturity date of a thirty year bond or mortgage.

You store the ISO date, you don't need a UNIX epoch to calculate that

You "store" it that way (as a string? no), but how are the relevant systems (database, OS, application) handling calculations with it?

that many years ago it was probably stored on tape and processed on a mainframe by a COBOL program. No database, it was probably stored as a string, and the application would likely have been fix to handle it before it was a problem.

That COBOL program almost certainly _is_ a database – not a SQL database but the COBOL language standard specifies structured records and allows you to specify whether a file is sequential or indexed (see e.g. https://en.wikipedia.org/wiki/COBOL#Data_division).

In the case of Y2K, the problem was often that people defined a date filed as three values all declared as PIC 99 (i.e. a two-digit number). If they migrated to 4 digits, we're fine until Y10K. If they added a window (values less n are 19xx, etc.) or switched to Unix timestamps or a SQL database – there are products which let a COBOL runtime transparently map the indexed file semantics to SQL statements – then it requires more information to say whether it's at risk.

I work with a COBOL database, and interestingly all of the dates are declared as four PIC 99 fields, like this:

    02  ITM-LAST-CHG-DATE.
      03  ITM-LC-DATE-CC    PIC 99.
      03  ITM-LC-DATE-YY    PIC 99.
      03  ITM-LC-DATE-MM    PIC 99.
      03  ITM-LC-DATE-DD    PIC 99.

I assume this split between 'CC' and 'YY' is a relic of the Y2K updates, so they could add in a whole new field with a default of '19'.

mysql> SELECT TIMESTAMPDIFF(YEAR,CURDATE(), "2050-01-01");

  +---------------------------------------------+
  | TIMESTAMPDIFF(YEAR,CURDATE(), "2050-01-01") |
  +---------------------------------------------+
  |                                          33 |
  +---------------------------------------------+

Seems to work fine

That's just the database, and one that we happen to know works.

The original comment referred to exactly this database, so it is relevant

You should never store or work with dates as unix epoch-based timestamps. Use proper date/time datatypes, and manipulate them with library functions. Naive integer arithmetic on unix timestamps will bite you in the ass a hundred different ways.

Certainly a good idea, but sometimes it's difficult to tell if OS functions and 3rd party libraries you depend on are doing that.

The ext4 filesystem in Linux, for example, uses epoch time. I think they have it all fixed now, but there were bugs still out there as recently as this year: https://bugzilla.kernel.org/show_bug.cgi?id=23732

> Use proper date/time datatypes, and manipulate them with library functions.

The point is: What do they do? There are plenty that will use... Unix timestamps (or worse). Or use those as the lowest common denominator for interchange. There are more systems at play than the data layer, and all of them will take every opportunity to bite you in the arse.

http://stackoverflow.com/questions/12067697/convert-current-...

One of the top results for "convert date to integer". People who have an integer on one hand, a datetime on the other are going to see this code and use it (hey, it works) without understanding the ramifications.

I suppose one can only hope that such code is brittle enough that other problems bring it down before 2038.

Reply on news.ycombinator.com