Get only the date in timestamp in mysql

Question

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!

Answer 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'

Answer 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

Answer 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

Answer 4

$date= new DateTime($row['your_date']) ;  
echo $date->format('Y-m-d');