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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users