I have a MySQL database, which I cannot alter, where I read date from. The issue is that I have a varchar column that stores a date. The date is stored in the atomtime format eg. 2014-06-01T00:00:00+02:00.
I cannot figure how to specify the format in the STR_TO_DATE function. I tried STR_TO_DATE(Endtime, '%Y-%m-%dT%H:%i:%s+02:00'), but that doesn't work.
Do anyone have a solution for this?
I am trying to run the following query (which is not working properly):
SELECT *, COUNT(*) as antal
FROM ivocall_calls
WHERE Agentname LIKE 'Vinh Nguyen'
AND Status1 = 'SALG'
AND STR_TO_DATE(Endtime, '%Y-%m-%dT%H:%i:%s+02:00')
BETWEEN STR_TO_DATE('2014-06-01T00:00:00+02:00', '%Y-%m-%dT%H:%i:%s+02:00')
AND STR_TO_DATE('2014-06-30T00:00:00+02:00', '%Y-%m-%dT%H:%i:%s+02:00')
Thanks in advance.
This is probably best-addressed using a stored function to parse and convert the timestamps from the stored format into MySQL's native format, using the built-in date time math functions to do the time zone conversions.
The function below will correctly handle two formats, YYYY-MM-DDTHH:MM:SSZ and YYYY-MM-DDTHH:MM:SS+/-HH:MM as well as correctly formed MySQL datetime literals, which will be passed through unmodified.
DELIMITER $$
DROP FUNCTION IF EXISTS `from_iso8601_subset` $$
CREATE FUNCTION `from_iso8601_subset`(in_ts TINYTEXT) RETURNS DATETIME
DETERMINISTIC
NO SQL
BEGIN
-- this function takes an input timestamp value in a suppported subset of iso8601 values, and
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone. Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.
-- if you are taking the value here and stuffing it into a DATETIME column, you need to have your
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(from_iso('input value'),'UTC','Your Desired Time Zone');
-- 2014-02-01T23:59:59Z --
IF (in_ts REGEXP '^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}[T ][[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}(Z|[+-][[:digit:]]{2}:[[:digit:]]{2})$') THEN
SET in_ts = REPLACE(REPLACE(in_ts, 'T', ' '), 'Z', '+00:00');
RETURN CONVERT_TZ(SUBSTRING(in_ts FROM 1 FOR 19), SUBSTRING(in_ts FROM 20 FOR 24), @@time_zone);
-- unexpected format -- let MySQL's built-in functions do the best they can; this will throw warnings
-- if the input is not a yyyy-mm-dd hh:mm:ss datetime literal; alternately this could return NULL.
ELSE
RETURN CAST(in_ts AS DATETIME);
END IF;
END $$
DELIMITER ;
Example output:
mysql> SET @@time_zone = 'America/New_York';
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT from_iso8601_subset('2014-06-01T00:00:00+02:00');
+--------------------------------------------------+
| from_iso8601_subset('2014-06-01T00:00:00+02:00') |
+--------------------------------------------------+
| 2014-05-31 18:00:00 |
+--------------------------------------------------+
1 row in set (0.08 sec)
mysql> set @@time_zone = 'UTC';
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT from_iso8601_subset('2014-06-01T00:00:00+02:00');
+--------------------------------------------------+
| from_iso8601_subset('2014-06-01T00:00:00+02:00') |
+--------------------------------------------------+
| 2014-05-31 22:00:00 |
+--------------------------------------------------+
1 row in set (0.08 sec)
We assume that if the input data matches one of the patterns, then the contents of the value passed in are also going to be sane; if you give nonsense input values, you'll get some nonsense output, such as if you are using a time zone of '+99:00' but it won't fail. This function does not have any SQL injection vulnerabilities.
The code could be further-optimized, but as written, this function is efficient enough that it can evaluate several thousand expressions per second on a moderately-powered machine.