0

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.

0

@@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`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.