1

Maybe a duplicate, but cannot find a clear response.

I have a MySQL DB with both 'datetime' and 'timestamp' columns. I can compare them with query, and everything I do with one type can be done with the other the exact same way.

I do know that internally, they are stored differently, but in my application (laravel & phpmyadmin) they are both shown as "2021-08-05 12:00:00", even the timestamp.

When I update a model with timestamp column, I end up doing

$model->timestampColumn = strftime('%Y-%m-%d %H:%M:%S', time());

instead of a more logic

$model->timestampColumn = time();

If I update a model with a timestamp column with the second method (with laravel or raw query or phpmyadmin), I end up with the same error "incorrect datetime value", as "timestamp" was considered alias of "datetime".

Concretely, it's not a bug or a behavior that I want to change, but I want to understand why a timestamp column is not treated as a number (with eventually a datetime formatted display).

This question is NOT a question about witch one of timestamp or datetime should I use in my application. I know the advantage and inconvenient of both. The question is more at engine level, like "why this is designed like this and why the behavior is confusing" more than "which is better for my purpose".

Tom.L
  • 171
  • 5
  • 4
    Check: https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql#:~:text=TIMESTAMP%20is%20four%20bytes%20vs,%3AMM%3ASS'%20format. – Ergest Basha Aug 05 '21 at 10:40
  • 1
    @basha04 *Check and : dbrnd.com/2015/09/…* This article contains wrong information. – Akina Aug 05 '21 at 10:50
  • Those articles show when you should use one instead of other depending of the context. But my question is more about the implementation / behavior of the MySQL engine, not the use cases (which I do not have any problem). Timestamp is a number, but used like a date in MySQL engine – Tom.L Aug 05 '21 at 10:58
  • 2
    *Timestamp is a number* In MySQL? No. – Akina Aug 05 '21 at 11:22
  • Does this answer your question? [Should I use the datetime or timestamp data type in MySQL?](https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql) –  Aug 05 '21 at 12:45
  • @Tom.L: in (standard) SQL" a `timestamp` is a well defined data type and is **not** "a number". You are probably thinking about the dreaded "unix epoch" when you think about a timestamp being a number. But that's not a timestamp as "SQL" understands it. You might want to read: https://blog.sql-workbench.eu/post/epoch-mania/ why using unix epoch in a SQL datatabase isn't really such a great idea. –  Aug 05 '21 at 12:57

1 Answers1

0

For the ones that wonders, my confusion & questioning was in fact a terminologie subtlety :

  • A Unix Timestamp is a number of second since "1970-01-01 00:00:00 UTC" (no timezone)

  • A Timestamp is a sequence of characters or encoded information identifying when a certain event occurred. It could be a Unix Timestamp, but can be the string "The first day of the year 1970 at midnight". No specification or standard are established (more infos : https://en.wikipedia.org/wiki/Timestamp)

  • A Timestamp in SQL is a standardized Timestamp in the way of MySQL, it is an arbitrary format made for optimization which implement timezone and allow fast formatting in the format YYYY-MM-DD HH:MM:SS. So it's not a simple number in the Unix Timestamp way, but more than that. It's also the reason that some developper implement BigInt column instead of datetime/timestamp to allow number manipulation in the way of an Unix Timestamp instead of an SQL Timestamp.

References for better understanding :

MySQL documentation / Wikipedia Timestamp / Blog

Thanks @a_horse_with_no_name for pointing out the needed elements for comprehension.

Tom.L
  • 171
  • 5