How to Convert Timezone in MySQL Using Timezone Name

MySQLmysql-5.5timezone

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is

SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') AS test
FROM appointment

which is working properly. Now the issue I am having is that my database uses the timezone name as America/New_York.

Is there any way to convert timezone using timezone name not from timezone offset?

Best Answer

To answer the question:

Is there any way to convert timezone using timezone name not from timezone offset

The function CONVERT_TZ has this example and warning:

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
    -> '2004-01-01 13:00:00'

Note To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

And the section on Time Zone support has a 'Populating the Time Zone Tables' section which describes the process of setting up the mysql timezone tables.

Related Solutions

MySQL – Query to Find Records Between Two Date Ranges

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends). Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
  FROM employee e
  LEFT JOIN coaching c ON c.employee_id = e.id
                      AND c.end_date >= '2014-12-26'
                      AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4

MySQL – How to Select All Mondays Between Two Dates

From here, I modified the query slightly to get

select adddate('2015-02-01', numlist.id) as `my_date`, 
       weekday(adddate('2015-02-01', numlist.id)) as day_no,
       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

which gives

+------------+--------+----------+
| my_date    | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 |      0 | Monday   |
| 2015-02-03 |      1 | Tuesday  |
| 2015-02-09 |      0 | Monday   |
| 2015-02-10 |      1 | Tuesday  |
| 2015-02-16 |      0 | Monday   |
| 2015-02-17 |      1 | Tuesday  |
| 2015-02-23 |      0 | Monday   |
| 2015-02-24 |      1 | Tuesday  |
+------------+--------+----------+
8 rows in set (0.01 sec)

or from here

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

result

+---------------+
| selected_date |
+---------------+
| 2015-02-02    |
| 2015-02-03    |
| 2015-02-09    |
| 2015-02-10    |
| 2015-02-16    |
| 2015-02-17    |
| 2015-02-23    |
| 2015-02-24    |
+---------------+
8 rows in set (0.23 sec)


CREATE TABLE mydate( blah date);

INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
--       weekday(adddate('2015-02-01', numlist.id)) as day_no,
--       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

(need a table num)

mysql> select * from mydate;
+------------+
| blah       |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |

insert into mydate (blah) 
select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

No table necessary. Same result as above.

Best Answer

Related Solutions

MySQL – Query to Find Records Between Two Date Ranges

MySQL – How to Select All Mondays Between Two Dates

Related Question