[Gambas-user] Using dates in Gambas

[Benoît Minisini]

Le 22/05/2019 à 11:28, David Silverwood via User a écrit :
> Thanks Bruce. I will give it a try!
> 
> Hi, Benoit. I have read the wiki, in fact, my browser currently opens on 
> it. But Gambas/Ubuntu is not playing along. I don't know whether 
> en_ZA.UTF-8 is not supported or badly supported but when gambas loads 
> the date from the sqlite db it is loaded as mm/dd/yyyy hh:mm:ss before 
> attempted conversion and mm/dd/yyyy after attempted conversion, and not 
> as yyyy-mm-dd. My problem comes in with a user(s) having to update the 
> database. It means we have to change the date back to sqlite3 format 
> every time we update a record and with one record having four odd 
> different dates that can become quite time consuming not to mention 
> tedious. Every conversion so far I have attempted has resulted in Gambas 
> literally 'arguing' and 'fixing' it for me...
> 

OK, you read it, but I'm not sure you understood it then.

"Dates are NOT strings" is the name of the first paragraph. Look at your 
words, you are talking about sending / receiving dates as strings from 
sqlite.

On the other hand, I didn't write the database-specific paragraph, so I 
will explain you:

1) You must send to the database and receive from it dates, not strings.

2) You have to create the database from Gambas, by using Date fields.

3) The gambas database component will take care of using the accurate 
date/time SQL type that works with your specific database.

4) To write a date to the database, you use: 'Result!Field = SomeDate'.

5) 'SomeDate' must be a date. If you use a string, you deal with 
implicit conversion.

6) To read the date back, you use 'SomeDate = Result!Field'.

7) 'SomeDate' is still a date. Now if you want to show the date to the 
user, (or to you), you have to use PRINT, Str(), or Format().

8) SQL date/time field types are a mess (as everything in SQL). The 
gambas database component will store the date in the database IN LOCAL 
TIME. Which is a stupid thing as soon as your database is accessed from 
different timezones. You have to manually convert the dates from the 
timezone of the server that writes to the database to the timezone of 
the client that reads it. An option for automatically doing that must be 
added to the Gambas database driver!

Are things more clear now?

-- 
Benoît Minisini