Why this datetime to timestamp convertion in PHP doesn't work?

Question

I want to convert this format of datetime "31-12-2018 19:30 hs." from Argentina to an UTC timestamp, I am using the following code:

$clean_date = substr($date, 0, -4);
$dt = new DateTime($clean_date, new DateTimeZone('America/Argentina/Buenos_Aires'));
$dt->setTimeZone(new DateTimeZone('UTC'));
$timestamp = $dt->getTimestamp();

But it doesn't work, in the database the record is "0000-00-00 00:00:00", but if I echo the $dt, till there is working perfectly and showing the datetime in UTC.

Could someone please help me? Thanks.

I think this should help you.[Click Here](https://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php/10978523) — Yash, Jan 01 '19 at 09:21
And $timestamp is also correct if you echo ? Your field in database use timestamp or datetime ? If its datetime, you need to convert your timestamp back to datetime: $value_for_database = date('Y-m-d H:i:s', $timestamp); — Steffen Mächtel, Jan 01 '19 at 09:30
The code that inserts data into MySQL could be broken. You need to post those details, too. — Salman A, Jan 01 '19 at 09:33
The $timestamp echo prints something like this: "1546335960", and the field type in database is timestamp, and the code that I'm using to update the row is this one: `$sql = "UPDATE $tblname SET nombre='$name', creado='$timestamp', msg='$msg' WHERE id=$rowid";`, obviously the other fields are being updated without problems. — Leandro, Jan 01 '19 at 09:51
Is it always Argentina to UTC you want to convert between? If year then it's always the same number of hours + DST — Andreas, Jan 01 '19 at 10:06

Álvaro González · Accepted Answer · 2019-01-01T10:58:31.967

This has nothing to do with PHP. You're simply using the incorrect date literal format in MySQL. A per the docs:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts.

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date.

1546335960 could be the last case but numbers don't make sense as date because year 1546 did not have 33 months.

To make it worse, many MySQL Servers are configured by default to let these kind of errors slip through:

mysql> CREATE TABLE test (
    ->     foo TIMESTAMP
    -> );
Query OK, 0 rows affected (0.74 sec)

mysql> SET @@SESSION.sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (foo) VALUES (1546335960);
Query OK, 1 row affected, 1 warning (0.39 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'foo' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test;
+---------------------+
| foo                 |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

As you can see, you got a mere warning (that you need to read explicitly) and data corruption.

If you configure your app to use a strict mode you'll get a proper error message just in time:

mysql> SET @@SESSION.sql_mode = 'TRADITIONAL,NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (foo) VALUES (1546335960);
ERROR 1292 (22007): Incorrect datetime value: '1546335960' for column 'foo' at row 1
mysql>

Please note that timestamp is just a generic English word:

A digital record of the time of occurrence of a particular event.

It isn't necessarily synonym for Unix time.

@Andreas Yes, it is. But nowhere in MySQL documentation do they say that Unix timestamp is a valid format. You actually need to [call a function](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime) to preprocess the value. — Álvaro González, Jan 01 '19 at 10:55
Incredible, more than helpful @Álvaro González, I will work on this and when I make it work I will give you the check! THANK you a lot. — Leandro, Jan 01 '19 at 11:12

Why this datetime to timestamp convertion in PHP doesn't work?

1 Answers1