CODEWITHSUNDEEP
mysqlsqldate-formatting
remyremy
remyremy

Reputation: 3758

Convert timestamp to date in MySQL query

I want to convert a timestamp in MySQL to a date.

I would like to format the user.registration field into the text file as a yyyy-mm-dd.

Here is my SQL:

$sql = requestSQL("SELECT user.email, 
                   info.name, 
                   FROM_UNIXTIME(user.registration),
                   info.news
                   FROM user, info 
                   WHERE user.id = info.id ", "export members");

I also tried the date conversion with:

DATE_FORMAT(user.registration, '%d/%m/%Y')
DATE(user.registration)

I echo the result before to write the text file and I get :

email1;name1;DATE_FORMAT(user.registration, '%d/%m/%Y');news1

email2;name2;news2

How can I convert that field to date?

Upvotes: 306

Views: 729400

Answers (13)

Mohammad Sadegh Rafiei
Mohammad Sadegh Rafiei

Reputation: 516

You can use this command FROM_UNIXTIME(unix_timestamp, [format]); but sometimes timestamp is in a long value that you have to remove 3 last values to 0. for instance you use this command from_unixtime(e.EVENT_TIME/1000); this way solve my problem.

Upvotes: 2

Alitta Varghese
Alitta Varghese

Reputation: 31

I want to convert a record 1580707260

Usually, I am using online timestamp converter

Want to showcase it in the query result

Please try this

DATE_FORMAT(FROM_UNIXTIME(field name from table), '%e %b %Y')

AS 'display name for result'

Upvotes: 3

Ekaterina
Ekaterina

Reputation: 171

If you want to change the datatype of the column, you can simply convert first from TIMESTAMP to INT:

ALTER TABLE table_name MODIFY column_name INT;

And then INT to DATE:

ALTER TABLE table_name MODIFY column_name DATE;

But, if you didn't mean to change a column, but wanted SELECT only, then you can use date() function:

SELECT date(your_timestamp_column) FROM your_table;

Upvotes: 3

d.raev
d.raev

Reputation: 9546

FROM_UNIXTIME(unix_timestamp, [format]) is all you need

FROM_UNIXTIME(user.registration, '%Y-%m-%d') AS 'date_formatted'

FROM_UNIXTIME gets a number value and transforms it to a DATE object,
or if given a format string, it returns it as a string.

The older solution was to get the initial date object and format it with a second function DATE_FORMAT... but this is no longer necessary

Upvotes: 18

juergen d
juergen d

Reputation: 204746

To just get a date you can cast it

cast(user.registration as date)

and to get a specific format use date_format

date_format(registration, '%Y-%m-%d')

SQLFiddle demo

Upvotes: 106

Eric Leschinski
Eric Leschinski

Reputation: 153842

Convert timestamp to date in MYSQL

Make the table with an integer timestamp:

mysql> create table foo(id INT, mytimestamp INT(11));
Query OK, 0 rows affected (0.02 sec)

Insert some values

mysql> insert into foo values(1, 1381262848);
Query OK, 1 row affected (0.01 sec)

Take a look

mysql> select * from foo;
+------+-------------+
| id   | mytimestamp |
+------+-------------+
|    1 |  1381262848 |
+------+-------------+
1 row in set (0.00 sec)

Convert the number to a timestamp:

mysql> select id, from_unixtime(mytimestamp) from foo;
+------+----------------------------+
| id   | from_unixtime(mytimestamp) |
+------+----------------------------+
|    1 | 2013-10-08 16:07:28        |
+------+----------------------------+
1 row in set (0.00 sec)

Convert it into a readable format:

mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo;
+------+-------------------------------------------------+
| id   | from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') |
+------+-------------------------------------------------+
|    1 | 2013 8th October 04:07:28                       |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 90

Emanuel Graf
Emanuel Graf

Reputation: 766

I did it with the 'date' function as described in here : 

(SELECT count(*) as the-counts,(date(timestamp)) as the-timestamps FROM `user_data` WHERE 1 group BY the-timestamps)

Upvotes: 3

Jaydeep Mor
Jaydeep Mor

Reputation: 1703

You should convert timestamp to date.

select FROM_UNIXTIME(user.registration, '%Y-%m-%d %H:%i:%s') AS 'date_formatted'

FROM_UNIXTIME

Upvotes: 9

Vitaliy A
Vitaliy A

Reputation: 3838

Try: 

SELECT strftime("%Y-%d-%m", col_name, 'unixepoch') AS col_name

It will format timestamp in milliseconds to yyyy-mm-dd string.

Upvotes: 1

Morey
Morey

Reputation: 629

Just use mysql's DATE function:

mysql> select DATE(mytimestamp) from foo;

Upvotes: 14

Yatin
Yatin

Reputation: 5380

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Upvotes: 524

Fraser
Fraser

Reputation: 17039

If you are getting the query in your output you need to show us the code that actually echos the result. Can you post the code that calls requeteSQL?

For example, if you have used single quotes in php, it will print the variable name, not the value

echo 'foo is $foo'; // foo is $foo

This sounds exactly like your problem and I am positive this is the cause.

Also, try removing the @ symbol to see if that helps by giving you more infromation.

so that 

$SQL_result = @mysql_query($SQL_requete); // run the query

becomes 

  $SQL_result = mysql_query($SQL_requete); // run the query

This will stop any error suppression if the query is throwing an error.

Upvotes: 3

cs0lar
cs0lar

Reputation: 367

If the registration field is indeed of type TIMESTAMP you should be able to just do:

$sql = "SELECT user.email, 
           info.name, 
           DATE(user.registration), 
           info.news
      FROM user, 
           info 
     WHERE user.id = info.id ";

and the registration should be showing as yyyy-mm-dd

Upvotes: 24

Related Questions