You can simply use the fromtimestamp function from the DateTime module to get a date from a UNIX timestamp. This function takes the timestamp as input and returns the corresponding DateTime object to timestamp.
MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Use FROM_UNIXTIME()
:
SELECT
FROM_UNIXTIME(timestamp)
FROM
your_table;
See also: MySQL documentation on FROM_UNIXTIME()
.
What's missing from the other answers (as of this writing) and not directly obvious is that from_unixtime
can take a second parameter to specify the format like so:
SELECT
from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM
your_table
I think what you're looking for is FROM_UNIXTIME()
Here's a one liner if you have quick access to the mysql cli:
mysql> select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';
+---------------------+
| local time |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+
Replace 'MST'
with your desired timezone. I live in Arizona 🌵 thus the conversion from UTC to MST.
Why bother saving the field as readable? Just us AS
SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
FROM theTable
WHERE theTable.theField = theValue;
EDIT: Sorry, we store everything in milliseconds not seconds. Fixed it.
You can use the DATE_FORMAT function. Here's a page with examples, and the patterns you can use to select different date components.
Easy and simple way:
select from_unixtime(column_name, '%Y-%m-%d') from table_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With