mysql - Combine 3 columns (date, time, timezone as VARCHAR) and represent as UTC -


due specific reasons storing events' dates 3 separate columns (instead of single datetime column):

day_begin (column type: date) hour_begin (column type: time) timezone (column type: varchar) 

here's example row of data data these 3 columns:

2015-01-27 09:00:00 europe/rome 

based on information, select , rows, start date within specific utc interval (e.g. these utc start utc end of january 2015).

this quite trivial if time stored in single datetime column, since can't change product accomodate specific need - need work mysql gives functions compare utc.

solution:

select convert_tz(concat_ws(" ", day_begin, hour_begin), timezone, "utc") 

the function concatenates date , hour columns , uses value timezone column second parameter convert_tz() handles time zone conversions.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -