CakePHP | How to change Epoch timestamp (unix timestamp) timezone in cakephp find query

|
| By Webner

Suppose we have a table cake_test with a column named modified that contains epoch values.

1

CakePHP code:

$options = array (
'fields' => array (
"modified",
"to_timestamp(modified) as New__original",
"to_timestamp(modified) AT TIME ZONE 'America/Denver' as New__converted"
)
);
$result = $this->CakeTest->find ( 'all', $options );

In the above query we have three columns: modified, original and converted.
Modified: Displaying saved epoch values.
Original: Converting epoch to timestamp.
Converted: Converting epoch value into timestamp with “America/Denver” timezone.

Output of above find function:

Array(
[0] => Array(
[CakeTest] => Array(
[modified] => 1496197500
)
[new] => Array(
[original] => 2017-05-31 07:55:00+05:30
[converted] => 2017-05-30 20:25:00
)
)
[1] => Array(
[CakeTest] => Array(
[modified] => 1496194500
)
[new] => Array(
[original] => 2017-05-31 07:05:00+05:30
[converted] => 2017-05-30 19:35:00
)
)
[2] => Array(
[CakeTest] => Array(
[modified] => 1496195500
)
[new] => Array(
[original] => 2017-05-31 07:21:40+05:30
[converted] => 2017-05-30 19:51:40
)
)
)

So, in this way we can display the time value in a particular timezone which was originally saved in a different timezone (UTC by default).

Leave a Reply

Your email address will not be published. Required fields are marked *