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

[Holger Hans Peter Freyther]

Module: kamailio
Branch: master
Commit: 4afc00bb3071bfbd5d4d5f36c7a5f1822c92a5f8
URL: https://github.com/kamailio/kamailio/commit/4afc00bb3071bfbd5d4d5f36c7a5f1822c92a5f8

Author: Holger Hans Peter Freyther <holger at moiji-mobile.com>
Committer: Holger Hans Peter Freyther <holger at moiji-mobile.com>
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/4afc00bb3071bfbd5d4d5f36c7a5f1822c92a5f8.diff
Patch: https://github.com/kamailio/kamailio/commit/4afc00bb3071bfbd5d4d5f36c7a5f1822c92a5f8.patch

---

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;