yii2, turn existing unix values into an timestamp inside the query

Question

Working on an exciting project which as columns with Unix timestamp instead of a timestamp, so I need to make these values readable timestamps, is there a way to do this inside the query itself?

     $query = (new \yii\db\Query())
     ->select(['a.created_at',
               'a.updated_at',
                ])
     ->from(['a' => 'product'])
     ->all();

// the idea

'created_at' => date('Y-m-d H:i:s')

Muhammad Omer Aslam · Accepted Answer · 2018-05-08 14:21:13Z

2

This should work for you

$query = (new \yii\db\Query())
     ->select([new \yii\db\Expression('from_unixtime(a.updated_at) as updatedAt,
                                       from_unixtime(a.created_at) as createdAt')])
     ->from(['a' => 'product'])
     ->all();

or you can use FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x'); to create a desired format like 2015 13th November 10:08:01 2015. see HERE for more details.

edited May 8, 2018 at 14:21

answered May 8, 2018 at 14:16

Muhammad Omer Aslam

23.9k9 gold badges47 silver badges69 bronze badges

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

8 Comments

Muhammad Omer Aslam Over a year ago

ok let me try it first on my machine will repost again @user759235

Muhammad Omer Aslam Over a year ago

can you show theschema details for the fields how are you saving unix timestamp into thee table ? @user759235

user759235 Over a year ago

As this project is not build by me I cant say, but for what I do know the column is an int(11), and when I use yii2 formatter it will return correct date/time.

Muhammad Omer Aslam Over a year ago

ok can you copy any of the timestamp saved and the replace it with the timestamp in this query SELECT FROM_UNIXTIME(1255033470); and try running it does it show you the date correctly? @user759235

Muhammad Omer Aslam Over a year ago

i just verified it and it works correctly if i save the unix timestamp using $model->created_at = new \yii\db\Expression ( 'UNIX_TIMESTAMP()' ); and then retrieve it in readable form using from_unixtime() it prints the date correctly using above query, this is strange do you have the latest yii version ? @user759235

|

Collectives™ on Stack Overflow

yii2, turn existing unix values into an timestamp inside the query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related