How to display the right timestamp when select query in mysql?

Question

I would like display the right timestamp of my query. The date in my database is this one and date timestamp is set in Europe/Berlin. That is in my server setup and I always get this result.

2017-05-26 09:05:58

I want to display the date from my query. here is the code of my query

select logs.action_date_time from AuditLogs

How can I convert the logs.action_date_time date in my place here in Philippines? I supposed to see this result

2017-05-26T09:05:58+02:00

I search it in google the unix_timestamp but I don't see any sample that they use in select before from query. I'm using symfony for this. I just give summary of what the result of my above sample code.

const SELECT =
        'SELECT logs.id, logs.object_id AS objectId, logs.object_type AS objectType, logs.object_name AS objectName, '.'logs.action, logs.action_date_time AS actionDateTime, logs.action_status AS actionStatus, logs.subject, logs.user, '.'logs.description, logs.ip, logs.param03_value AS userId, logs.param04_value AS userDisplayName ';

    public function getNext($params)
    {
        // Hardcode for now
        $limit = $params['limit'] ? (int) $params['limit'] : self::DEFAULT_LIMIT;
        $limit++;

        $select = self::SELECT;
        $whereClause = $this->constructWhereClause($params);

        $sql = "
            $select FROM spoke.audit_logs AS logs
            $whereClause
            ORDER BY logs.action_date_time DESC, logs.id DESC
            LIMIT $limit";

        if ($params['cursor']) {
            $now = new \DateTime();
            $datetime = $now->getTimestamp();
            if (isset($params['datetime'])) {
                $datetime = $params['datetime'];
            }
            $whereClause = $this->constructWhereClause($params, 'logs1', false);
            $sql = "
                $select
                FROM spoke.audit_logs logs1, spoke.audit_logs logs
                WHERE logs1.id = logs.id 
                    $whereClause
                    AND logs1.action_date_time <= '$datetime'
                    AND (logs1.id < {$params['cursor']} OR logs1.action_date_time < '$datetime')
                ORDER BY logs1.action_date_time DESC, logs1.id DESC
                LIMIT $limit";
        }
        $stmt = $this->getEntityManager()->getConnection()->query($sql);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);

    }

Darshan Mehta · Answer 1 · 2017-05-26T07:41:44.883

1

You need to use MySQL's CONVERT_TZ function to convert the timezones, e.g.:

SELECT CONVERT_TZ(action_date_time, 'CEST', 'PHT') FROM AuditLogs;

Alternatively, you can provide time zone difference as well, e.g.:

SELECT CONVERT_TZ(action_date_time,'+01:00','+08:00') FROM AuditLogs;

Here's the documentation.

edited May 26 '17 at 07:41

answered May 26 '17 at 07:27

Darshan Mehta

30,102
11
68
102

If add this my select CONVERT_TZ(logs.action_date_time, 'CEST', 'PHT') AS actionDateTime and I found null in my response – user3818576 May 26 '17 at 07:35
@user3818576 added another query, try now? – Darshan Mehta May 26 '17 at 07:41

score 1 · Accepted Answer · answered May 26 '17 at 07:45

1

To get a UTC datetime column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`

Source is here: Link

answered May 26 '17 at 07:45

Nawin

1,653
2
14
23

I try to convert this but the time advance 2 hours. – user3818576 May 26 '17 at 08:05
but I try the link you give me – user3818576 May 26 '17 at 08:06

How to display the right timestamp when select query in mysql?

2 Answers2