CODEWITHSUNDEEP
mysql
Waris Ali
Waris Ali

Reputation: 522

Mysql - Convert local date in UTC format

Good day,

I am having mysql dates stored in this format :

enter image description here

In my sql query I want to get these dates in UTC format. I have tried CONVERT_TZ(dt,from_tz,to_tz)function but I cannot determine how can I get from_tz for the dates. I know that to_tz will be 'UTC' or '+0000'

Upvotes: 2

Views: 560

Answers (2)

Blank
Blank

Reputation: 12378

Try this:

select convert_tz(`date`, replace(substring_index(`date`, ' ', -1), '00', ':00'), '+00:00')

Edit:

select convert_tz(`date`, concat(left(substring_index(`date`, ' ', -1), 3), ':', right(substring_index(`date`, ' ', -1), 2)), '+00:00')

Upvotes: 1

Alexey
Alexey

Reputation: 2478

Well, as mysql ignores TZ qualifiers in date, I suggest you do a semimanual TZ conversion

select         
    date_add("2017-04-18 15:15:15  +1000", interval substring("2017-04-18 15:15:15 +1000", -5, 3) hour),
    date_add("2017-04-18 15:15:15  +1000", interval substring("2017-04-18 15:15:15 -1000", -5, 3) hour)
;

the result will be in UTC timezone as you expect

Upvotes: 0

Related Questions