Get only the date in timestamp in mysql

On my database under the t_stamp columns I have the date for example

2013-11-26 01:24:34

From that same column I only want to get the date

2013-11-26

How can i do this? Thank You!

Solution 1:

You can use date(t_stamp) to get only the date part from a timestamp.

You can check the date() function in the docs

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03'); -> '2003-12-31'

Solution 2:

You can convert that time in Unix timestamp by using

select UNIX_TIMESTAMP('2013-11-26 01:24:34')

then convert it in the readable format in whatever format you need

select from_unixtime(UNIX_TIMESTAMP('2013-11-26 01:24:34'),"%Y-%m-%d");

For in detail you can visit link

Solution 3:

To get only date from timestamp in MySQL just use DATE_FORMAT(datetime, '%Y-%m-%d'):

SELECT DATE_FORMAT('2013-11-26 01:24:34', '%Y-%m-%d');
> 2013-11-26