[mysql] 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?

This question is related to mysql sql date-formatting

The answer is


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

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


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)

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.


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


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)

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


Try:

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

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


You should convert timestamp to date.

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

FROM_UNIXTIME


you can try this
The date is of timestamp type which has the following format: ‘YYYY-MM-DD HH:MM:SS’ or ‘2008-10-05 21:34:02.’

$res = mysql_query("SELECT date FROM times;");
while ( $row = mysql_fetch_array($res) ) {
   echo $row['date'] . "
";
}

The PHP strtotime function parses the MySQL timestamp into a Unix timestamp which can be utilized for further parsing or formatting in the PHP date function.

Here are some other sample date output formats that may be of practical use:

echo date("F j, Y g:i a", strtotime($row["date"]));                  // October 5, 2008 9:34 pm
echo date("m.d.y", strtotime($row["date"]));                         // 10.05.08
echo date("j, n, Y", strtotime($row["date"]));                       // 5, 10, 2008
echo date("Ymd", strtotime($row["date"]));                           // 20081005
echo date('\i\t \i\s \t\h\e jS \d\a\y.', strtotime($row["date"]));   // It is the 5th day.
echo date("D M j G:i:s T Y", strtotime($row["date"]));               // Sun Oct 5 21:34:02 PST 2008

Just use mysql's DATE function:

mysql> select DATE(mytimestamp) from foo;

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to date-formatting

How to insert date values into table How do I format a date as ISO 8601 in moment.js? Java Convert GMT/UTC to Local time doesn't work as expected Compare two date formats in javascript/jquery How to ISO 8601 format a Date with Timezone Offset in JavaScript? What are the "standard unambiguous date" formats for string-to-date conversion in R? How to convert date in to yyyy-MM-dd Format? Convert timestamp to date in MySQL query Date formatting in WPF datagrid Return date as ddmmyyyy in SQL Server