Page 1 of 1

mysql and British Summer Time problem

Posted: Monday 12th April 2021 8:10am
by bill-lancaster
A date-time value is converted from British Summer Time to UTC when writing a mysql record.

Code: Select all

  hResult = hConn.create("date_test")
  hResult!DateTimeCreated = Now
  hResult!DateTimeEvent = Date(2021, 4, 16, 9, 35, 00)
The resulting time values in the mysql db are 1 hour less.

This is probably a mysql matter but I wonder if anyone can help?

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 9:54am
by BruceSteers
bill-lancaster wrote: Monday 12th April 2021 8:10am A date-time value is converted from British Summer Time to UTC when writing a mysql record.

Code: Select all

  hResult = hConn.create("date_test")
  hResult!DateTimeCreated = Now
  hResult!DateTimeEvent = Date(2021, 4, 16, 9, 35, 00)
The resulting time values in the mysql db are 1 hour less.

This is probably a mysql matter but I wonder if anyone can help?
Date functions mostly deal with UTC so local conversion will be needed.
I'd give this page a good read..
http://gambaswiki.org/wiki/doc/date

so if you store the date value it is UTC , only when recalling it using date functions does it convert to local time.

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 10:22am
by bill-lancaster
Thanks Bruce, still confused though!
In gambas print date(), time() all show the right value.
My system clock is correct
Mysql locale is shown as "System"
I'm thinking its really an sql matter

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 12:32pm
by BruceSteers
bill-lancaster wrote: Monday 12th April 2021 10:22am Thanks Bruce, still confused though!
In gambas print date(), time() all show the right value.
My system clock is correct
Mysql locale is shown as "System"
I'm thinking its really an sql matter
Date value is stored as a UTC value (not locally conveted)
Using Date() and Time() do the local string conversion.

Maybe if you convert the date to a local string format first and then store that in the db not the utc value.
wiki wrote: Dates are not strings
The confusion mainly comes from the fact that you are thinking about dates by using its locale string representation, i.e. as if the Date datatype would represent a locale date. Big mistake!

Internally, Gambas stores a Date value in Universal Time (a.k.a. "UTC"), using two 32 bits integer:

The first integer is a number of days since a specific epoch, which is January 1st, 4801 BC. It's the date part.

The second integer is a number of milliseconds since midnight. It's the time part.

But there is no way in Gambas to directly write a Date constant. You always has to use either the Date function that builds a date or convert a string representation of the date by using CStr, CDate, Str$ or Val.

Consequently, each time you manipulate a date representation, Gambas has to decide if your date representation is in U.T.C. or in local time. In other words, the timezone associated with a date representation is implicit.

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 4:09pm
by bill-lancaster
Yes, you're right, in fact the time values in the sql database are correct, its when they are displayed as a string that the problem occurs.
So, when displayed in gridview (gdvTest),or in string Svar the time values are wrong. hResult!DateTimeEvent (an sql date-time field) is correct when printed to the console.

Code: Select all

    gdvTest[i, 1].Text = hResult!DateTimeEvent
    sVar = hResult!DateTimeEvent
    Print hResult!DateTimeEvent;; sVar
I'm going to have to think about this.
Thanks again

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 4:16pm
by bill-lancaster
Either the value is adjusted before writing to the db or the adjustment is made when reading the db.
I favour the latter, CDATE() displays the correct (local) time value.
Thanks again.

Re: mysql and British Summer Time problem

Posted: Monday 12th April 2021 5:47pm
by BruceSteers
bill-lancaster wrote: Monday 12th April 2021 4:16pm Either the value is adjusted before writing to the db or the adjustment is made when reading the db.
I favour the latter, CDATE() displays the correct (local) time value.
Thanks again.
You're welcome.. there's always a way :)

Re: mysql and British Summer Time problem

Posted: Wednesday 14th April 2021 7:04am
by bill-lancaster
Time(mysql!DateTime) also shows British Summer Time

Re: mysql and British Summer Time problem

Posted: Wednesday 14th April 2021 8:07am
by BruceSteers
bill-lancaster wrote: Wednesday 14th April 2021 7:04am Time(mysql!DateTime) also shows British Summer Time
yep , like i said in my first reply , date functions (Time() and Date()) do local conversion.