Menu
I have a table with the column create_time which is of type INTEGER and represents the time since epoch.
I'd like to select all rows and columns, while displaying this row as a date/time in UTC format.
How do I do that?
301
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.
To get a day of week from a timestamp, use the DAYOFWEEK() function: -- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday SELECT dayofweek('2018-12-12'); -- returns the string day name like Monday, Tuesday, etc SELECT dayname(now()); To convert a timestamp to a unix timestamp (integer seconds):
MySQL recognizes DATETIME and TIMESTAMP values in these formats: As a string in either ' YYYY-MM-DD hh:mm:ss ' or ' YY-MM-DD hh:mm:ss ' format.
Use FROM_UNIXTIME to convert seconds since epoch to a DATETIME, which also allows you specify a format:
FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%s')
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
