What I have learned about DateTime

What are you using B4X for? What have you learnt along the way?

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:
SELECT CAST(strftime('%s', SalesDate) AS INTEGER)
We need to be careful when storing the value whether it has included the milliseconds or not? Check the values return from B4X is having extra 3 digits or not.

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:

datetime('now', 'localtime'),
strftime('%s000', 'now'),
strftime('%s000', 'now', 'localtime')

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.

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.