0

In my DB there is a date-time (slot_start) field. Unfortunately the entries are expressed in two different time zones. For example: 2021-02-08T08:00:00+01:00 and 2021-02-08T07:00:00+00:00

Now I'm trying to list all entries in Central European Time (CET).

I have tried the following:

SELECT customer_nicename,
   service_name,
   CONVERT_TZ(slot_start,'+00:00','+00:00') AS Slot_Start,
   status
FROM reservations
WHERE status LIKE 'confirmed'

But one of the two time zones are expressed shifted by an hour.

I dont get any further - can somebody help me out? Thanks a lot!

Shadow
  • 33,525
  • 10
  • 51
  • 64
HermannMeyer
  • 1
  • Here all looks great : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=219e3e7ffc511ee5c0f3eb65ff303d14 What is your MySQL version ? – VBoka Jan 18 '21 at 13:42
  • Hi it is Version 5.7.28. – HermannMeyer Jan 18 '21 at 16:01
  • Hi, when choose version 5.7 I can not enter the data examples from your question as you can see here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=219e3e7ffc511ee5c0f3eb65ff303d14 Can you do it ? – VBoka Jan 19 '21 at 11:47
  • No there is an error: "Incorrect datetime value: '2021-02-08T08:00:00+01:00' for column 'slot_start' at row 1" coming up. – HermannMeyer Jan 19 '21 at 12:01
  • @VBoka thank you so far. Do you know any other SQL query to get the timezones converted? – HermannMeyer Jan 19 '21 at 12:10
  • Well how did you get that data in the column slot_start in your database ? Can you help me insert them in my small database on this link so I can try to help you... – VBoka Jan 19 '21 at 12:12
  • Sorry, the error is only on dbfiddle.uk not in my query showing up.[link](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=219e3e7ffc511ee5c0f3eb65ff303d14) – HermannMeyer Jan 19 '21 at 12:34
  • Imagine you have two columns like in my example and show me the way you are inserting data in your database ? – VBoka Jan 19 '21 at 12:38
  • Well, unfortunately I don't know how the date-time values are getting inserted into the db. A wordpress booking plugin is doing the job. – HermannMeyer Jan 20 '21 at 12:43

0 Answers0