user2899728 user2899728 - 6 months ago 15
SQL Question

MySQL: Why CAST() function gives wrong date?

I am trying to get date part from timestamp field.
I used this sql query:

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


I got following result:

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


As shown above date field produced wrong date 16 whereas original date is 15.

Please guide that why is that happening?

How can we resolve this issue?

I'll appreciate any contribution.

Thanks

Answer

Use date not cast because is not casting but a format

select timestamp, date(timestamp) as my_date from messages