[sr-dev] git:master:4afc00bb: db_mysql: Let the database convert the timestamp

Module: kamailio Branch: master Commit: 4afc00bb3071bfbd5d4d5f36c7a5f1822c92a5f8 URL: https://github.com/kamailio/kamailio/commit/4afc00bb3071bfbd5d4d5f36c7a5f18… Author: Holger Hans Peter Freyther &lt;holger(a)moiji-mobile.com&gt; Committer: Holger Hans Peter Freyther &lt;holger(a)moiji-mobile.com&gt; Date: 2017-02-01T09:36:18+01:00 db_mysql: Let the database convert the timestamp Add option to use FROM_UNUXTIME(time_t) instead of using the time2str routines. This makes MySQL apply the database timezone instead of using the localtime of the system inserting the data. This is useful for the sipcapture module where the system time might be in CET but the database is using UTC. In these cases the micro_ts and the date columns were offseted by the timezone difference. --- Modified: src/modules/db_mysql/db_mysql.c Modified: src/modules/db_mysql/db_mysql.h Modified: src/modules/db_mysql/doc/db_mysql_admin.xml Modified: src/modules/db_mysql/km_val.c --- Diff: https://github.com/kamailio/kamailio/commit/4afc00bb3071bfbd5d4d5f36c7a5f18… Patch: https://github.com/kamailio/kamailio/commit/4afc00bb3071bfbd5d4d5f36c7a5f18… --- diff --git a/src/modules/db_mysql/db_mysql.c b/src/modules/db_mysql/db_mysql.c index 5ab3acb..a2ba563 100644 --- a/src/modules/db_mysql/db_mysql.c +++ b/src/modules/db_mysql/db_mysql.c @@ -41,6 +41,7 @@ unsigned int my_connect_to = 2; /* 2 s by default */ unsigned int my_send_to = 0; /* enabled only for mysql >= 5.25 */ unsigned int my_recv_to = 0; /* enabled only for mysql >= 5.25 */ unsigned int my_retries = 1; /* Number of retries when command fails */ +unsigned int my_server_timezone = 0; /* Use FROM_UNIXTIME() for date conversion */ unsigned long my_client_ver = 0; @@ -91,6 +92,7 @@ static param_export_t params[] = { {"send_timeout", PARAM_INT, &my_send_to}, {"receive_timeout", PARAM_INT, &my_recv_to}, {"retries", PARAM_INT, &my_retries}, + {"server_timezone", PARAM_INT, &my_server_timezone}, {"timeout_interval", INT_PARAM, &db_mysql_timeout_interval}, {"auto_reconnect", INT_PARAM, &db_mysql_auto_reconnect}, diff --git a/src/modules/db_mysql/db_mysql.h b/src/modules/db_mysql/db_mysql.h index 44eb985..f762a2c 100644 --- a/src/modules/db_mysql/db_mysql.h +++ b/src/modules/db_mysql/db_mysql.h @@ -44,6 +44,7 @@ extern unsigned int my_send_to; extern unsigned int my_recv_to; extern unsigned long my_client_ver; extern unsigned int my_retries; +extern unsigned int my_server_timezone; /** @} */ diff --git a/src/modules/db_mysql/doc/db_mysql_admin.xml b/src/modules/db_mysql/doc/db_mysql_admin.xml index b791c6a..e15b85a 100644 --- a/src/modules/db_mysql/doc/db_mysql_admin.xml +++ b/src/modules/db_mysql/doc/db_mysql_admin.xml @@ -74,6 +74,27 @@ modparam("db_mysql", "ping_interval", 600) </programlisting> </example> </section> + <section> + <title><varname>server_timezone</varname> (integer)</title> + <para> + Control if kamailio convers a time to string using the local timezone + or if MySQL will convert it using FROM_UNIXTIME. This option is useful + if kamailio and the database are configured for different timezones. + </para> + <para> + <emphasis> + Default value is 0 (use local conversion) + </emphasis> + </para> + <example> + <title>Set <varname>server_timezone</varname> parameter</title> + <programlisting format="linespecific"> +... +modparam("db_mysql", "server_timezone", 1) +... +</programlisting> + </example> + </section> <section> <title><varname>timeout_interval</varname> (integer)</title> <para> diff --git a/src/modules/db_mysql/km_val.c b/src/modules/db_mysql/km_val.c index fd93d9b..905daa5 100644 --- a/src/modules/db_mysql/km_val.c +++ b/src/modules/db_mysql/km_val.c @@ -29,6 +29,7 @@ #include "../../lib/srdb1/db_ut.h" #include "km_val.h" #include "km_my_con.h" +#include "db_mysql.h" /*! @@ -46,6 +47,27 @@ int db_mysql_val2str(const db1_con_t* _c, const db_val_t* _v, char* _s, int* _le int l, tmp; char* old_s; + switch(VAL_TYPE(_v)) { + case DB1_DATETIME: + if (my_server_timezone) { + /* Let MySQL handle timestamp to internal time representation */ + if (!_s || !_len || !*_len) { + LM_ERR("Invalid parameter value\n"); + return -1; + } + l = snprintf(_s, *_len, "FROM_UNIXTIME(%d)", VAL_INT(_v)); + if (l < 0 || l >= *_len) { + LM_ERR("Error in snprintf\n"); + return -1; + } + *_len = l; + return 0; + } + break; + default: + break; + }; + tmp = db_val2str(_c, _v, _s, _len); if (tmp < 1) return tmp;