mysql and British Summer Time problem

Post your Gambas programming questions here.
Post Reply
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

mysql and British Summer Time problem

Post 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?
User avatar
BruceSteers
Posts: 1523
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: mysql and British Summer Time problem

Post 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.
If at first you don't succeed , try doing something differently.
BruceS
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post 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
User avatar
BruceSteers
Posts: 1523
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: mysql and British Summer Time problem

Post 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.
If at first you don't succeed , try doing something differently.
BruceS
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post 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
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post 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.
User avatar
BruceSteers
Posts: 1523
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: mysql and British Summer Time problem

Post 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 :)
If at first you don't succeed , try doing something differently.
BruceS
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post by bill-lancaster »

Time(mysql!DateTime) also shows British Summer Time
User avatar
BruceSteers
Posts: 1523
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: mysql and British Summer Time problem

Post 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.
If at first you don't succeed , try doing something differently.
BruceS
Post Reply