I am developing a POS with B4X using SQLite as the local database for the clients. I thought I am very experience in dealing with SQL and databases. I am very wrong!
In the past, I always store date and time data in table column as String in this format yyyy-MM-dd HH:mm:ss. It always work well. In some databases, we have datetime type of fields.
BUT, SQLite is a very simple database engine. There is no datetime type. You can either choose to save as TEXT or INT.
This time, I try to save the timestamp or date data as Long. I thought it will have some advantages or make things more simpler in the future.
Then now I found I am facing some issues. it is not fun to save the timestamp or datetime values as Ticks or UNIXEPOCH.
From my experience in programming since VB6, DateTime handling is always a number 1 issue. Let alone the Leap year, timezone and locale formatting, now I found that it is not straight forward for Java and SQLite talk to each other.
It sounds ideal to store the Ticks (number of milliseconds from 1970-01-01 00:00:00.000). B4X views like DatePicker and B4XTable returns or reads the value in Ticks. However, here are some lessons learned.
Lesson #1: Divide by 1000
Here is an example of converting the data into Long:
SQL:
SELECT CAST(strftime('%s', SalesDate) AS INTEGER)Lesson #2: Creating a Default value
It is not straight forward like MySQL or MSSQL where you can put now() or getdate() built-in function. In SQLite, it is a bit more complicated to create an automatic default value for your new row Created_Date in table schema. I am not sure, I think this has improved in new version of SQLite.
Lesson #3: DateTime.Now vs getting localtime from SQLite query
Even I am on a local machine, there are difference between the B4X DateTime.Now and ticks return from SQLite. This is due to UTC or different timezone. Do some experiments with:
B4X:
Log(DateTime.Now)
Log(Datetime.Date(DateTime.Now))
SQL:
SELECT
datetime('now'),
datetime('now', 'localtime'),
strftime('%s000', 'now'),
strftime('%s000', 'now', 'localtime')[SQLite] datetime('now')
B4A Code #Region Project Attributes #ApplicationLabel: LocalTime #VersionCode: 1 #VersionName: 1.0 'SupportedOrientations possible values: unspecified, landscape or portrait. #SupportedOrientations: unspecified #CanInstallToExternalStorage: False #End Region #Region...
www.b4x.com
Lesson #4: localtime vs unixepoch
The issue raises when I store my sales date in SQLite as localtime ticks (UTC+8). When I sync my sales data to a remote server having UTC+0 and the VPS doesn't allow me to change the system timezone, the results become wrong. I see future date values. To workaround, we need to see in what situation, we need to use 'localtime' or 'unixepoch' in the SQL.
Conclusion:
If we didn't provide the right value to the SQLite query, then our resultset will return wrong or no results. In my case, when I want to generate a report for example Current day Sales, I need to pass the value of today's date StartOfDay and EndOfDay to my query. If both values are not correct, I may get wrong results.
