How to convert timestamp to datetime in MySQL?

Mysql

Mysql Problem Overview

How to convert 1300464000 to 2011-03-18 16:00:00 in MySQL?

Mysql Solutions

Solution 1 - Mysql

Use the FROM_UNIXTIME() function in MySQL

Remember that if you are using a framework that stores it in milliseconds (for example Java's timestamp) you have to divide by 1000 to obtain the right Unix time in seconds.

Solution 2 - Mysql

DATE_FORMAT(FROM_UNIXTIME(`orderdate`), '%Y-%m-%d %H:%i:%s') as "Date" FROM `orders`

This is the ultimate solution if the given date is in encoded format like 1300464000

Solution 3 - Mysql

To answer Janus Troelsen comment

Use UNIX_TIMESTAMP instead of TIMESTAMP

SELECT from_unixtime( UNIX_TIMESTAMP(  "2011-12-01 22:01:23.048" ) )

The TIMESTAMP function returns a Date or a DateTime and not a timestamp, while UNIX_TIMESTAMP returns a unix timestamp

Solution 4 - Mysql

You can use

select from_unixtime(1300464000,"%Y-%m-%d %h %i %s") from table;

For in details description about

  1. from_unixtime()
  2. unix_timestamp()

Solution 5 - Mysql

SELECT from_unixtime( UNIX_TIMESTAMP(fild_with_timestamp) ) from "your_table"
This work for me

Categories
Recommended Mysql Solutions

Previous Solution:

MySQL integer field is returned as string in PHP

PhpMysqlTypesIntGettype

Next Solution:

SQL Server Output Clause into a scalar variable

Sql ServerTsql

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questioncompile-fanView Question on Stackoverflow
Solution 1 - MysqlRichard TuinView Answer on Stackoverflow
Solution 2 - MysqlKingshuk DebView Answer on Stackoverflow
Solution 3 - MysqlksvendsenView Answer on Stackoverflow
Solution 4 - MysqlDilraj SinghView Answer on Stackoverflow
Solution 5 - MysqlLeoView Answer on Stackoverflow