Why does the CAST() function return the wrong date?

Question

I am trying to get the date part from a timestamp field. I used this SQL query:

select timestamp, CAST(timestamp as date) as date from messages

I got the following result:

--------------------------------------------
|        timestamp        |      date      |
--------------------------------------------
|   2016-05-15 10:22:54   |   2016-05-16   |
--------------------------------------------

As shown above, the date field produced returns the wrong date 2016-05-16 whereas the original date is 2016-05-15.

How can we resolve this issue?

Answer 1

Thats not a issue !!! Its only set the wrong time_zone. see sample

get current time_zone

SHOW GLOBAL VARIABLES LIKE 'time_zone'; -- systemwide setting
SHOW VARIABLES LIKE 'time_zone'; -- session setting

sample

MariaDB [mysql]> select t, CAST(t as date) FROM groupme LIMIT 1;
+---------------------+-----------------+
| t                   | CAST(t as date) |
+---------------------+-----------------+
| 2016-05-15 20:22:54 | 2016-05-15      |
+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [mysql]> SET  time_zone ='-12:00';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select t, CAST(t as date) FROM groupme LIMIT 1;
+---------------------+-----------------+
| t                   | CAST(t as date) |
+---------------------+-----------------+
| 2016-05-14 20:22:54 | 2016-05-14      |
+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [mysql]>