2

I've happened onto a little confusion. Does MySQL store TIMESTAMP as unix time in seconds, e.g. 1541645714

Or is it stored in the ISO 8601 format 2018-11-08T02:55:14+00:00

I'm not sure why I always thought that the latter was only the input format, and that MySQL converted that format to Unix time. To me it would seem more efficient.

Can anyone clear this up for me? How is TIMESTAMP actually stored? I've looked through the documentation and can't seem to find any answers.

Thanks for your insight / help.

markbratanov
  • 878
  • 3
  • 17
  • 39
  • Please Tag your really using dbms thanks – D-Shih Nov 08 '18 at 03:13
  • 1
    This question is too broad. Each database has its own internal representation of the date, which may or may not be explained in its documentation. Of course, each database also has functions to extract components of the date, compare dates, and convert them to strings in desired formats. As an example, this Wikipedia article explains different dates used for "epochs": https://en.wikipedia.org/wiki/Epoch_(reference_date). – Gordon Linoff Nov 08 '18 at 03:14
  • @GordonLinoff I've narrowed it to just MySQL, if that helps. – markbratanov Nov 08 '18 at 03:16
  • 4
    https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html – Amadan Nov 08 '18 at 03:18
  • @Amadan this was EXACTLY what I was looking for. Thank you for linking it. – markbratanov Nov 08 '18 at 03:20
  • 1
    See also the sister site [*DBA Stack Exchange*](https://dba.stackexchange.com/) for related posts. – Basil Bourque Nov 08 '18 at 03:56

2 Answers2

4

TIMESTAMP in MySQL is 4-byte integer representing seconds since UNIX epoch (midnight 1970-01-01); in newer versions, it is extended to store sub-second precision by 0-3 bytes depending on the fsp (fractional second precision) parameter to data type.

Bonus: PostgreSQL has a compile-time parameter that determines whether TIMESTAMP is stored as seconds since midnight 2000-01-01 in a 8-byte integer, or in a double-precision floating number (EDIT: apparently no longer available in newer versions; thanks, Basil Bourque).

As you say, storing timestamps as text is hardly efficient. Furthermore, the weirdness of calendar jumps from complex to unbearably complex.

Note that the above is about TIMESTAMP; other date/time types may have different representations.

Amadan
  • 191,408
  • 23
  • 240
  • 301
2

You shouldn’t really care

Does MySQL store TIMESTAMP as a unix timestamp in seconds, e.g. 1541645714

Or is it actually stored in the ISO 8601 format 2018-11-08T02:55:14+00:00

You don't care. The internal representation is an implementation detail unrelated to your work. And that implementation could change in future versions.

What a database accepts for inputs or generates for outputs is not directly related to its internal representation.

All you should care about is the documented behavior, the SQL standard, and the delta between them.

In the case of date-time types, the SQL standard barely touches the subject, defining a few types and defining very little behavior. So the capabilities of databases vary widely.

Excerpts from MySQL documentation:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

fractional seconds part in up to microseconds (6 digits) precision … is discarded

Y2038 Problem

If you are curious about the implementation, look at the source code.

But without even looking at source, after reading this documentation we have two clues about the TIMESTAMP type:

  • Any fractional second dropped.
  • The upper limit is 2038-01-19T03:14:07Z.

So we know the type is likely stored as a 32-bit count of whole seconds since the epoch reference date used commonly in Unix and some other systems: the first moment of 1970 in UTC, 1970-01-01T00:00Z. Such a 32-bit count will reach its maximum on that date, in a phenomenon known in the industry as the Y2038 Problem.

If date-time handling is important to your project, you might consider using a database with more robust date-time handling, such as Postgres.

Smart objects, not dumb strings

You should be using the date-time types within your programming language to exchange date-time values with your database. Avoid communicating with mere strings.

Leading the industry in this regard is the java.time framework in Java 8 and later, with a back-port available for Java 6 & 7.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;
Strin sql = "SELECT * FROM event_ WHERE when_ >= ? ;" ;
…
myPreparedStatement.setObject( 1 , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ; // In Postgres and some other databases, the offset-from-UTC will be zero, for UTC itself.
ZonedDateTime zdt = odt.atZoneSameInstant( ZoneId.of( "America/Montreal" ) ) ;  // Adjust from UTC into a time zone. Same moment, same point on the timeline, different wall-clock time.

And proving my point… Amadan on other Answer points out this documentation I presented above is now outdated, from MySQL 5.5. The implementation did change radically in Version 8.

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Thanks for the insight. I was just curious how it was being stored behind the scenes. I'll read more documentation on postgres, seems to have some interesting features. – markbratanov Nov 08 '18 at 03:42
  • 1
    Indeed, calendar functions in Postgres are amazing - even 20 years ago, its datetime functions were stunning in comparison to competitors. – Amadan Nov 08 '18 at 03:43
  • 1
    @markbratanov Search Stack Overflow to learn more about Postgres handling of date-time. You need to learn about moments on the timeline (`TIMESTAMP WITH TIME ZONE`) and date-time values that are *not* moments, *not* on the timeline (`TIMESTAMP WITHOUT TIME ZONE`). And you should learn about learning to focus on UTC rather than zoned values. You will find many posts about this by me and others. Reading about Java and its modern *java.time* framework may help ground your understanding of the issues, even if you don't use Java. – Basil Bourque Nov 08 '18 at 03:44
  • @BasilBourque will do. Thanks again for your insight. Much appreciated. I do use Java, but mainly within the Android framework (not sure how much that counts). I'll definitely look into it. – markbratanov Nov 08 '18 at 03:48
  • 1
    @markbratanov For Android <26, see: [*How to use ThreeTenABP in Android Project*](https://stackoverflow.com/questions/38922754/how-to-use-threetenabp-in-android-project). For Android >=26, use *java.time* built-in. – Basil Bourque Nov 08 '18 at 03:52