change the integer time to date format MM/DD/YY like datetime.fromtimestamp in python-CodePudding

I have a table as follow. In python with datetime.fromtimestamp we can change the integer time to date. I want to change the time column to MM/DD/YY in Mysql. Can you help me with that?

CodePudding user response：

mysql> select from_unixtime(1546322400) as date;
 --------------------- 
| date                |
 --------------------- 
| 2018-12-31 22:00:00 |
 --------------------- 

mysql> select date_format(from_unixtime(1546322400), '%m/%d/%Y') as date;
 ------------ 
| date       |
 ------------ 
| 12/31/2018 |
 ------------

I forgot until the comment from nbk above that FROM_UNIXTIME() does support an optional second argument, so you can do this in one step:

mysql> select from_unixtime(1546322400, '%m/%d/%Y') as date;
 ------------ 
| date       |
 ------------ 
| 12/31/2018 |
 ------------

Read about DATE_FORMAT() and FROM_UNIXTIME().

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime is the simplest version

SET @a = 1546322400

SELECT FROM_UNIXTIME(1546322400,  '%d/%m/%y')

| FROM_UNIXTIME(1546322400,  '%d/%m/%y') |
| :------------------------------------- |
| 01/01/19                               |

db<>fiddle here