Convert timestamp to date in MySQL query
MysqlSqlDate FormattingMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'
Solution 2 - Mysql
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')
Solution 3 - Mysql
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)
Solution 4 - Mysql
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
Solution 5 - Mysql
Just use mysql's DATE function:
mysql> select DATE(mytimestamp) from foo;
Solution 6 - Mysql
You should convert timestamp
to date
.
select FROM_UNIXTIME(user.registration, '%Y-%m-%d %H:%i:%s') AS 'date_formatted'
Solution 7 - Mysql
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
Solution 8 - Mysql
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.
Solution 9 - Mysql
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)
Solution 10 - Mysql
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;
Solution 11 - Mysql
Try:
SELECT strftime("%Y-%d-%m", col_name, 'unixepoch') AS col_name
It will format timestamp in milliseconds to yyyy-mm-dd string.