mysql and British Summer Time problem

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

mysql and British Summer Time problem

Post by bill-lancaster » 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?

User avatar
BruceSteers
Posts: 478
Joined: Thursday 23rd July 2020 5:20pm

Re: mysql and British Summer Time problem

Post by BruceSteers » Monday 12th April 2021 9:54am

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.
Wishing well
Bruce

If at first you don't succeed , try it differently.

bill-lancaster
Posts: 130
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post by bill-lancaster » 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

User avatar
BruceSteers
Posts: 478
Joined: Thursday 23rd July 2020 5:20pm

Re: mysql and British Summer Time problem

Post by BruceSteers » Monday 12th April 2021 12:32pm

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.
Wishing well
Bruce

If at first you don't succeed , try it differently.

bill-lancaster
Posts: 130
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post by bill-lancaster » Monday 12th April 2021 4:09pm

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: 130
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post by bill-lancaster » 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.

User avatar
BruceSteers
Posts: 478
Joined: Thursday 23rd July 2020 5:20pm

Re: mysql and British Summer Time problem

Post by BruceSteers » Monday 12th April 2021 5:47pm

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 :)
Wishing well
Bruce

If at first you don't succeed , try it differently.

bill-lancaster
Posts: 130
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: mysql and British Summer Time problem

Post by bill-lancaster » Wednesday 14th April 2021 7:04am

Time(mysql!DateTime) also shows British Summer Time

User avatar
BruceSteers
Posts: 478
Joined: Thursday 23rd July 2020 5:20pm

Re: mysql and British Summer Time problem

Post by BruceSteers » Wednesday 14th April 2021 8:07am

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.
Wishing well
Bruce

If at first you don't succeed , try it differently.

Post Reply