Mysql – convert date to timestamp and store into table using trigger thesql

mariadbmariadb-10.1MySQL

Using trigger I convert DateTime into a timestamp and then store into another table. But somehow convert date into timestamp always affected by timezone so that's why I had use convert_tz of date and then try to store in the database but it's not working for me.

TRIGGER : 

DELIMITER $$
CREATE TRIGGER `insert_cdrs_data` AFTER INSERT ON `cdrs` FOR EACH ROW
BEGIN
        insert into cdrs_data1(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate, @@global.time_zone, '+0:00')),new.accountid,new.type,new.calldate);
    insert into cdrs_data2(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)),new.accountid,new.type,new.calldate);
END;
$$
DELIMITER ;

Here we can see that for table cdrs_data1
UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,@@global.time_zone,'+0:00')

For the table: cdrs_data2

UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)

OUTPUT :

cdr_data 1 : 

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
    +---------------------+------------+--------------------+---------------------+
    | calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
    +---------------------+------------+--------------------+---------------------+
    | 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
    +---------------------+------------+--------------------+---------------------+

cdr_data2:

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

Setting global and session timezone into +03+00.

mysql> SET @@session.time_zone='+03:00';SET @@global.time_zone='+03:00';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558761964 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data2 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558783564 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.00 sec)

After change database session and global timezone value timestamp value is not correct showing in above queries.

Question :
How to store date into timestamp using trigger in GMT+0:00 without change timezone or in other terms its should not affect by timezone.

Note :
Right now its not possible to restart mysql so i can't set timezone into the my.cnf file.

Best Answer

@@global.time_zone variable

To see what value they are set to

SELECT @@global.time_zone;

To set a value for it use either one:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

@@session.time_zone variable

SELECT @@session.time_zone;

To set it use either one:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

both "@@global.time_zone variable" and "@@session.time_zone variable" might return "SYSTEM" which means that they use the timezone set in "my.cnf".

For timezone names to work (even for default-time-zone) you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Note: you can not do this as it will return NULL:

SELECT 
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` 
FROM `table_name`
Related Question