B4J Question [Solved] MySQL DATE datatype leads to wrong date?

[udg]

Hi all,
today I observed a surprising event.
A field in a MySQL DB is set as DATE datatype. If at the console I enter something like
INSERT INTO mytable (ddn) VALUES('1960-01-01')
using Toad I see that field as 01/01/1960 12:00:00 AM. So everything looks ok.

Using B4J, I make use of an ADP (AnotherDatePicker) to read its Date property (a long datatype), store it in a Map, trasmit the map over the Internet, read again as a Map, set DateFormat to "yyyy-MM-dd" to comply with MySQL and finally execute an INSERT into my table.

When I read it back (or look at it through Toad) I see it as '12/31/1959 12:00:00 AM'.
What could it be?

Here's my code before inserting into the table

'NUser is the map received from the client using ADP
Dim temp As String = DateTime.DateFormat      'save current format for later restoring
DateTime.DateFormat = "yyyy-MM-dd"
Dim ddn As String = DateTime.Date(NUser.Get("ddn"))
Dim sql1 As SQL = pool.GetConnection
...
sql1.ExecNonQuery2($"INSERT INTO mytable
     (id,name,ddn,addr1)
     VALUES(?,?,?,?)"$, _
     Array As Object(Null, NUser.Get("name"), ddn, NUser.Get("addr1")))  
...

Note: the server (hosting both MySQL and a B4J "server" component) is located in a different Timezone then the PC running the client (which uses ADP to collect the date to be recorded).

udg

 

[mindful]

Note: the server (hosting both MySQL and a B4J "server" component) is located in a different Timezone then the PC running the client (which uses ADP to colelct the date to be recorded).

It's most probably because of the different timezone ... note that if you work with clients which are in different timezones then you should save the UTC date in the mysql and apply timezone only when viewing the date.

 

[udg]

I was suspecting TZ too, but my surprise is due to the kind of datatype, a DATE (btw it is the birthday in a personal data record).
I checked in B4J and the time part is all zeros in the long vars used.
I know that I could store the data as a string but would like to understand where the day change happens.

 

[mindful]

As I remeber the Long date is in UTC tz ... so maybe the change happens when you are parsing into your date format - Dim ddn AsString = DateTime.Date(NUser.Get("ddn")). you can try to set your timezone to 0 before parsing that long date like:

Dim SystemTZOffset As Double = DateTime.TimeZoneOffset ' save the current timezone offset for later restoring
DateTime.SetTimeZone(0) ' set the timezone offset to 0 before you are parsing the long date
Dim temp AsString = DateTime.DateFormat 'save current format for later restoring
DateTime.DateFormat = "yyyy-MM-dd"
Dim ddn AsString = DateTime.Date(NUser.Get("ddn"))
....

 

[udg]

Hi mindful,
thanks for your support. Unfortunately the TZ "patch" applied to the server component alone didn't work.
Probably I'll have to use it on the client side and trasmit a clean string (formatted as yyyy-mm-dd) between my c/s components, ready to be used in the INSERT command.

Edit: I set the TZ=0 in my client, trasmitted the date as a long var, set TZ=0 in the server receiving end , read the long var and converted it to string with DT.Date. It works correctly.