Convert timestamp to date in MySQL query

Question

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?

Its saving the file exactly like that? What is the code you're using to run the sql query? — seanbreeden, Feb 12 '12 at 18:49

score 500 · Accepted Answer · edited Aug 23 '13 at 15:21

500

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

edited Aug 23 '13 at 15:21

Flimm

136,138
45
251
267

answered Sep 27 '12 at 20:59

Yatin

5,140
1
13
3

3

As of MySQL 5.6.23, I find that the value to FROM_UNIXTIME() should be entered directly: i.e., DATE_FORMAT(FROM_UNIXTIME(user.registration), '%e %b %Y') AS 'date_formatted' – JESii Apr 15 '16 at 13:50
2

SELECT * FROM users WHERE DATE_FORMAT(FROM_UNIXTIME(`users`.`user_created_at`),'%Y-%b-%e') = '2015-03-06' is this correct format or should i modify this? – Dheeraj Thedijje Mar 07 '18 at 11:49
3

@DheerajThedijje this shows up as 2018-Nov-6. You may be looking for `'%Y-%m-%d'` as you'd format it in PHP `(date('Y-m-d',$row->user_created_at))` - this (both variants, SQL and PHP) shows up as 2018-11-06 – Chris S. Nov 07 '18 at 09:19
2

In case you want to add the time in this fashion "hh:mm:ss", add this to the format string ' %H:%i:%s' – luis.ap.uyen Feb 18 '20 at 08:54
So is this storing the datetime of that timestamp from the perspective of London, or New York, or where the trigger is run or where the client is run? – William Entriken Nov 02 '22 at 16:22

juergen d · Answer 2 · 2019-07-25T18:48:29.970

98

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

edited Jul 25 '19 at 18:48

answered Feb 12 '12 at 18:48

juergen d

201,996
37
293
362

1

It gives the same result, cast(user.registration as date) is displayed instead of the value of the field – remyremy Feb 12 '12 at 19:10
6

This worked though - cast(from_unixtime(user.registration) AS date) – Eric Ness Nov 18 '15 at 16:12
i have specific date format that its worked for me as well as possible thanks – saber tabatabaee yazdi Jan 31 '18 at 22:46
1

use "as DATETIME" to get hour minutes and seconds too – Enrique Sep 27 '19 at 13:01

Eric Leschinski · Answer 3 · 2019-02-14T18:44:25.217

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)

score 24 · Answer 4 · answered Feb 12 '12 at 19:03

24

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

answered Feb 12 '12 at 19:03

cs0lar

367
1
2

I tried but I get the same result: DATE(user.registration) is display instead of the value of the field – remyremy Feb 12 '12 at 19:09
as someone suggested, are you able to run the query successfully using mysql client directly? I haven't heard of the requeteSQL function, what exactly does it do? – cs0lar Feb 12 '12 at 19:17
Unfortunately I am not able to use a mysql client directly, I don't have my own server... I have provided requeteSQL, basically it just run the query and test if error to send emails... – remyremy Feb 12 '12 at 19:22
2

Actually I realized my field was a BIGINT type and not a TIMESTAMP. Only the content was a timestamp (1328649722), so that's why it didn't work. Now everything is good with FROM_UNIXTIME! – remyremy Feb 13 '12 at 17:55

score 15 · Answer 5 · answered Aug 16 '19 at 09:30

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

score 13 · Answer 6 · answered Feb 03 '16 at 01:02

13

Just use mysql's DATE function:

mysql> select DATE(mytimestamp) from foo;

answered Feb 03 '16 at 01:02

Morey

549
7
10

7

`SELECT DATE(UNIX_TIMESTAMP())` spits `NULL` in my MySQL 5.7.14 – Xenos Apr 11 '17 at 20:35
try DATE(CURRENT_TIMESTAMP()) – Morey Aug 10 '17 at 02:52

score 8 · Answer 7 · answered Feb 28 '18 at 05:54

8

You should convert timestamp to date.

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

FROM_UNIXTIME

answered Feb 28 '18 at 05:54

Jaydeep Mor

1,690
3
21
39

score 3 · Answer 8 · answered Apr 24 '18 at 12:03

3

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)

answered Apr 24 '18 at 12:03

Emanuel Graf

756
17
37

Ekaterina · Answer 9 · 2021-10-10T11:17:13.290

3

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;

edited Oct 10 '21 at 11:17

answered Oct 10 '21 at 09:29

Ekaterina

101
1
3

score 3 · Answer 10 · answered Feb 12 '12 at 20:01

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.

score 2 · Answer 11 · edited Sep 13 '22 at 03:20

2

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'

edited Sep 13 '22 at 03:20

Willie Cheng

7,679
13
55
68

answered Sep 08 '22 at 07:32

Alitta Varghese

21
2

score 1 · Answer 12 · answered Feb 04 '16 at 14:13

1

Try:

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

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

answered Feb 04 '16 at 14:13

Vitaliy A

3,651
1
32
34

score 0 · Answer 13 · answered Feb 06 '23 at 09:37

0

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.

answered Feb 06 '23 at 09:37

Mohammad Sadegh Rafiei

486
1
5
14

Convert timestamp to date in MySQL query

13 Answers13

SQLFiddle demo

Convert timestamp to date in MYSQL

Linked

Related