6

Does anybody know the formal name of the YYYY-MM-DD HH:MM:SS date format used by a MySQL DATETIME? Is there one? It looks close to ISO 8601, but it's different enough to break in most applications if you try to use the two interchangeably.

Sometimes I find myself needing to refer to this particular format in conversation, but referring to it as "the DATETIME format" always draws blanks from those who haven't done much work with databases. I'm accustomed to date formats having official names and extremely formal definitions. (RFC 2822, anyone?) So, does one exist?

smitelli
  • 6,835
  • 3
  • 31
  • 53
  • I would have thought that *is* the ISO format. What is the difference to the ISO format? –  Dec 06 '12 at 15:04
  • 1
    It's close, but ISO has a `T` separating the date and time components instead of a space character. – smitelli Dec 06 '12 at 15:41
  • The ISO definition states that the `T` can be left out if no abmiguity is to be expected (at least that's what Wikipedia says) –  Dec 06 '12 at 15:51
  • Perhaps. But as a practical example, the JavaScript Date object in Firefox 17: `isNaN(Date.parse('2012-12-06 12:15:00')) === true` and `isNaN(Date.parse('2012-12-06T12:15:00')) === false`. The `T` does seem to make a difference in some applications. – smitelli Dec 06 '12 at 16:18
  • @smitelli the ISO standard is quite extensive and covers many variations. Sadly it's not open for anyone to read. Javascript supports a very restrictive subset. – Evert Jun 16 '21 at 06:50

2 Answers2

4

MySQL's Date and Time Literals are described in the reference manual. AFAICS, there's neither ISO nor any other name mentioned.

Wikipedia, ISO 8601 shows the ISO combined date and time format, which is "2012-12-04T20:03" ("YYYY-MM-DDTHH:MM") and differs from the mentioned MySQL datetime format.

So, although it looks roughly like an ISO datetime, I would just call it MySQL or SQL format.

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
3

2021

Olaf Dietsche's answer is right but old (2012) & slightly inaccurate as of 2021. According to Wikipedia ISO 8601:

As of ISO 8601-1:2019, the basic format is T[hh][mm][ss] and the extended format is T[hh]:[mm]:[ss]. Earlier versions omitted the T (representing time) in both formats.

Also from same link:

ISO 8601-1:2019 allows the T to be omitted in the extended format, as in "13:47:30", but only allows the T to be omitted in the basic format when there is no risk of ambiguity with date expressions.

MySQL in their documentation mention "ISO" (with no number), in one link they did mention that ISO format refers to ISO 9075, not ISO 8601..

ISO 9075 is basically MySQL standards.

btw, ISO 8601 & RFC 3339 are pretty much the same thing (from developers' perspective). In case you are interested, read.

Bottom line:

MySQL datetime format matches same format of ISO-8601 / RFC-3339 , although MySQL has its own standard ISO 9075. So, for the MySQL datetime format, we can refer to it as any of the below:

  • ISO 8601 aka RFC 3339
  • MySQL format aka ISO 9075.
evilReiko
  • 19,501
  • 24
  • 86
  • 102