I am using a program writing in C++ using MS Visual Studio 6 to read a SQLite
table. The table's create statement, as reported by SQLiteSpy, is:
CREATE TABLE trend_data( tag_key integer, value integer, value_timestamp
datetime );
I am trying to retrieve the largest value of the value_timestamp field in this
table using this query:
select max(value_timestamp) AS latest_time from trend_data
I am not sure what the standard method is for reading the value of a datetime
field from a C++ program. There is no sqlite3_column_datetime() function. As
I understand it, SQLite stores datetime values as Julian times, the number of
days since some time before 4700 BC. These are doubles, stored with enough
precision to identify microseconds. So, inside my application, I use the
sqlite3_column_double() function to retrieve the latest_time field from the
above query.
The problem I am running into, and have been running into for the two years
that I have been working with the ActiveX control that uses this database, is
that the formatting of the datetime value inside SQLite seems to be
inconsistent. Currently, the value read from the latest_time field is "2009".
I finally figured out why. For some reason I don't understand, SQLite is
converting the value_timestamp into a string of the form "2009-03-29 12:34:56"
before my call to sqlite3_column_double(). Because sqlite3 is typeless, when I
ask it for a double, SQLite tries to convert that string to a number, giving me
the number that is represented by the first numeric characters in that string.
But I swear I have seen this same query return a correct, valid datetime (a
double with a value of over 245,000) from this same query.
It seems that the only way to ensure that I get a valid datetime is to use the
julianday() function in my query:
select max(julianday(value_timestamp)) AS latest_time from trend_data.
I don't want to have to do that because that would be applying an extra
function call to every record in the table, and there could be hundreds of
thousands of them. But if I turn it around, as in:
select julianday(max(value_timestamp)) AS latest_time from trend_data.
so that I'm only applying the julianday() function to one record, I lose the
guarantee that I am actually working with datetimes. If SQLite converts the
value_timestamps to strings before finding the maximum, max(value_timestamp)
will still be 2009.
Is there some function I can call from my C++ application to ensure that
datetime values are never converted to strings unless I explicitly use the
datetime() function? Or is there some SQL statement I can use that will change
the database file to do that?
Thank you very much.
Rob Richardson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
