Menu
I will be storing datetime values in an SQLite database (using Delphi and the DISqlite library). The nature of the db is such that it will never need to be transferred between computers or systems, so interoperability is not a constraint. My focus instead is on reading speed. The datetime field will be indexed and I will be searching on it a lot, as well as reading in thousands of datetime values in sequence.
Since SQLite does not have an explicit data type for datetime values, there are several options:
use REAL data type and store Delphi's TDateTime values directly: fastest, no conversion from string on loading; impossible to debug dates using a db manager such as SQLiteSpy, since dates will not be human-readable. Cannot use SQLite date functions (?)
use a simple string format, e.g. YYYYMMDDHHNNSS: conversion is required but relatively easy on the CPU (no need to scan for separators), data is human-readable. Still cannot use SQLite date functions.
do something else. What's the recommended thing to do?
I have read http://www.sqlite.org/lang_datefunc.html but there's no mention of what data type to use, and, not being formally schooled in programming, I don't quite grok the focus on Julian dates. Why the additional conversion? I will be reading in these values a lot, so any additional conversions between strings and TDateTime adds a significant cost.
394
Date and Time Datatype. SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.
SQLite TIME() extracts the time part of a time or datetime expression as string format. The time() function returns the time as HH:MM:SS. Example-1: If you want to get the current time the following SQL can be used.
First, create a new table named datetime_real . Second, insert the “current” date and time value into the datetime_real table. We used the julianday() function to convert the current date and time to the Julian Day. Third, query data from the datetime_real table.
SELECT date('now','start of month','+13 month','-1 day') as "Last Date of current month after a Year"; Here is the result. Example-6: If you want to get the last date of the current month after 4 years, the following SQL statements can be used.
You could use one of the SQLite supported string formats, eg. YYYY-MM-DD HH:MM:SS.SSS.
It would be just as easy as YYYYMMDDHHNNSS - you still wouldn't need to scan for separators, since all the numbers are fixed length - and you would get SQLite date function support.
If you need SQLite date function support, I would go with that method.
If not, I'd recommend using REAL values. You can still compare them to each other (higher numbers are later in time), and consider date and time separately (before and after the decimal point respectively) without converting to TDateTime.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
