CODEWITHSUNDEEP
mysqlsql
user18147555
user18147555

Reputation:

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

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?

enter image description here

Upvotes: 1

Views: 89

Answers (2)

nbk
nbk

Reputation: 49375

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562368

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().

Upvotes: 1

Related Questions