MySQL view: convert unix timestamp to datetime

Question

How do I convert timestamps in a MySQL View?

I have a MySQL View for data exchange between 2 different software tools. The original table is a unix timestamp as int(11). The other software expects a DATETIME.

I have a VIEW like this:

CREATE VIEW `myView` AS 
select 
date_format(from_unixtime(`myUnixTimestamp`),_utf8'%d/%m/%Y %H:%i:%s') 
AS `Date-Entered`
from ...

This VIEW statement creates a varchar(24) - which is better than nothing as it can be converted - but the conversion makes some problems.

Is there any way to create a DATETIME field instead of a VARCHAR with the view?

feeela · Accepted Answer · 2012-10-26 09:37:10Z

3

You can cast the value to datetime:

-- print a UNIX timestamp (as returned by NOW()) as DATETIME
SELECT CAST( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) AS DATETIME );

answered Oct 26, 2012 at 9:37

feeela

30k7 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrea Schmuttermair · Accepted Answer · 2012-10-26 10:25:30Z

2

Thanks for the answers, I tried some combinations, and this one worked (so easy, its a shame...):

CREATE VIEW `myView` AS 
select 
from_unixtime(`myUnixTimestamp`)
AS `Date-Entered`
from ...

View Structure shows: Date-Entered, type: datetime

answered Oct 26, 2012 at 10:25

Andrea Schmuttermair

68012 silver badges29 bronze badges

Comments

Doc · Accepted Answer · 2012-10-26 09:40:33Z

-1

try

CREATE VIEW `myView` AS 
select 
FROM_UNIXTIME([unix timestamp],'%d/%m/%Y %H:%i:%s') 
AS `Date-Entered`
from ...

answered Oct 26, 2012 at 9:40

Doc

5,3566 gold badges62 silver badges94 bronze badges

Collectives™ on Stack Overflow

MySQL view: convert unix timestamp to datetime

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related