Understanding Date Conversion and "Ticks" in B4X and SQLite
When working with B4X and SQLite, correctly comparing or storing dates requires attention due to the differences in how both handle time.
Ticks in B4X (Android/Java)
- In B4X (using DateTime.Now), ticks are counted in milliseconds since January 1, 1970.
- These "ticks" are known as Unix Time in milliseconds.
- Example:
Dim ticksAndroid As Long = DateTime.Now
Log(ticksAndroid) ' Displays the current ticks in milliseconds
Ticks in SQLite
- SQLite uses Julian Day, which counts time in days (and fractions of days) from November 24, 4714 BC, at noon.
- The julianday('now') function returns the current time in Julian Day format.
- Example of Julian Day generated by SQLite:
This returns a value like 2460264.25, where the integer part represents days and the decimal part represents fractions of a day.
Converting B4X Ticks to Julian Day
Since B4X works with
milliseconds since January 1, 1970, and SQLite uses
days since November 24, 4714 BC, we need to convert
B4X ticks to the
Julian Day format used by SQLite.
Conversion Formula
Dim ticksAndroid As Long = DateTime.Now
Dim julianDay As Double = (ticksAndroid / 86400000.0) + 2440587.5
Log(julianDay) ' Displays the current date in Julian Day format
- ticksAndroid / 86400000.0: Converts milliseconds to days, since 1 day has 86,400,000 milliseconds.
- 2440587.5: This is the constant representing the number of days between November 24, 4714 BC (start of Julian Day) and January 1, 1970 (start of Unix Time).
Storing Dates in SQLite
When you want to store dates in SQLite consistently, the best practice is to use
Julian Day or
Unix Time in seconds.
Storing Dates as Julian Day
Create a table in SQLite with a field that uses julianday('now') to store the date automatically:
CREATE TABLE example (
id INTEGER PRIMARY KEY,
description TEXT,
creation_date REAL DEFAULT (julianday('now'))
);
Here, the creation_date field stores the insertion date in
Julian Day format.
Inserting Data Manually in SQLite
If you need to manually insert data with a specific date, use the conversion formula in B4X:
Dim ticksAndroid As Long = DateTime.Now
Dim julianDay As Double = (ticksAndroid / 86400000.0) + 2440587.5
Dim Query As String = "INSERT INTO example (description, creation_date) VALUES (?, ?)"
SQL1.ExecNonQuery2(Query, Array As Object("Test date", julianDay))
Querying and Comparing Dates
To query records based on a specific date, you can directly compare
Julian Day values in SQLite:
SELECT * FROM example WHERE creation_date > ?
In B4X, pass the converted
Julian Day date as a parameter:
Dim ticksAndroid As Long = DateTime.Now - (7 * DateTime.TicksPerDay) ' 7 days ago
Dim julianDay As Double = (ticksAndroid / 86400000.0) + 2440587.5
Dim Query As String = "SELECT * FROM example WHERE creation_date > ?"
Dim Cursor As ResultSet = SQL1.ExecQuery2(Query, Array As Object(julianDay))
Do While Cursor.NextRow
Log(Cursor.GetString("description"))
Loop
Cursor.Close
Alternative: Using Unix Time in Seconds
If you prefer working with
Unix Time in seconds instead of
Julian Day, the approach is simpler because both B4X and SQLite support Unix Time.
Steps to Use Unix Time
- Create the Table in SQLite with Unix Time
CREATE TABLE example (
id INTEGER PRIMARY KEY,
description TEXT,
creation_date INTEGER DEFAULT (strftime('%s', 'now'))
);
Here, strftime('%s', 'now') returns the current date and time in seconds since January 1, 1970.
- Insert Data in SQLite
Dim unixTime As Long = DateTime.Now / 1000 ' Convert to seconds
Dim Query As String = "INSERT INTO example (description, creation_date) VALUES (?, ?)"
SQL1.ExecNonQuery2(Query, Array As Object("Test Unix Time", unixTime))
- Query Records in SQLite
Dim unixTime As Long = DateTime.Now / 1000 ' Convert to seconds
Dim Query As String = "SELECT * FROM example WHERE creation_date > ?"
Dim Cursor As ResultSet = SQL1.ExecQuery2(Query, Array As Object(unixTime - (7 * 86400))) ' 7 days ago
Do While Cursor.NextRow
Log(Cursor.GetString("description"))
Loop
Cursor.Close
Conclusion
- If you need high precision and compatibility with SQLite's default format, use Julian Day with the conversion formula and the constant 2440587.5.
- If you prefer a simpler solution, use Unix Time in seconds, which eliminates the need for complex conversions since both Android and SQLite can work directly with Unix Time.
Both solutions are viable, depending on the level of precision and simplicity you want.