friskyweasel
asked on
sqlite database - timestamp, date, time
hello all -
i'm using the ADO.NET Data Provider for SQLite to insert some date / time records. the data is inserting fine, but once it's in my sqlite db, it's not behaving correctly when i attempt to do an ORDER BY, or if i attempt to return a range of fields (Stamp > 8/14/2006 03:45:00 AND Stamp < 8/17/2006 11:27:00), the query returns records that shouldn't be there, or omits ones that should be there
I'm using SQLite Administrator as the front-end tool for setting up the database...I've tried making the field a DATE, TIME, TIMESTAMP, and even manually entering in DATETIME...but this problem is still occurring
Is DATETIME supported by sqlite? bottom line is it doesn't matter to me at all how i accomplish it, but i want to be able to insert a date and time into one column in my sqlite db, and then be able to pull records while applying logic against that column (DATETIME greater than, DATETIME less than, etc...)
i'm using the ADO.NET Data Provider for SQLite to insert some date / time records. the data is inserting fine, but once it's in my sqlite db, it's not behaving correctly when i attempt to do an ORDER BY, or if i attempt to return a range of fields (Stamp > 8/14/2006 03:45:00 AND Stamp < 8/17/2006 11:27:00), the query returns records that shouldn't be there, or omits ones that should be there
I'm using SQLite Administrator as the front-end tool for setting up the database...I've tried making the field a DATE, TIME, TIMESTAMP, and even manually entering in DATETIME...but this problem is still occurring
Is DATETIME supported by sqlite? bottom line is it doesn't matter to me at all how i accomplish it, but i want to be able to insert a date and time into one column in my sqlite db, and then be able to pull records while applying logic against that column (DATETIME greater than, DATETIME less than, etc...)
Are you passing the parameters to the query as strings (ie. in a specific format) or DateTime objects? If you're using strings, you're making an assumption about the correct representation of the DateTime, so it's wiser to use DateTimes instead.
ASKER
i'm passing as strings right now, but i've tried both ways - and recently i've actually had success getting them to insert into the database...the problem is that after i insert 7 or 8 test records, i run a query that should exclude some of them, yet the query returns everything - could you possibly give me an example of a scenario that should work? i'm using a standard DateTimePicker control in .Net, and i'm building a sql string (.CommandText property), and then executing an insert against the database (.ExecuteNonQuery) - i've tried several different variations for formatting the date and time, as well as several different datatypes on the database side using sqlite admin (timestamp, date, time) - i'm able to insert the date and time using the 'timestamp' datatype - here's a quick example of what i'm doing:
my application generates the following sql string and executes it against the database:
INSERT INTO receipts(purchasestamp) VALUES ('2006-08-23 00:23:22')
everything works ok - when i view the record in the db using sqlite admin, it displays like this (this is with the datatype set to timestamp in the database):
8/23/2006 12:23:22 AM
notice sqlite took the "00:23:22" portion and correctly converted it to "AM" time...it also converted the hyphens to forward slashes on its own....this was an encouraging sign, and i assumed i had everything working correctly - however - if i insert several more records of varying dates/times, and attempt to do logical operations / sorting...i've noticed that they aren't sorted/compared correctly
my application generates the following sql string and executes it against the database:
INSERT INTO receipts(purchasestamp) VALUES ('2006-08-23 00:23:22')
everything works ok - when i view the record in the db using sqlite admin, it displays like this (this is with the datatype set to timestamp in the database):
8/23/2006 12:23:22 AM
notice sqlite took the "00:23:22" portion and correctly converted it to "AM" time...it also converted the hyphens to forward slashes on its own....this was an encouraging sign, and i assumed i had everything working correctly - however - if i insert several more records of varying dates/times, and attempt to do logical operations / sorting...i've noticed that they aren't sorted/compared correctly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot rytmis for the great insights...it seems to be working now, and everything you said was dead on - here's the scenario that worked for me in case others run into this:
sqlite version 3 database (.db3)
the datatype for the db field is "DATETIME" (sqlite admin didn't have this option by default - i typed it in)
finally, the format for the string to insert needs to be: YYYY-MM-DD HH:MM:SS (like u said rytmis)
the reason it wasn't ordering / sorting correctly for me was apparently because of how i formatted my select statement - when it inserts the value, it shows up in the database in this format: 8/23/2006 12:23:22 AM
i assumed my queries should use this same format:
select * from MyTable where MyField = '8/23/2006 12:23:22 AM'
but as it turns out, sqlite wanted it in the same format as the one i used to insert originally (YYYY-MM-DD HH:MM:SS)
select * from MyTable where MyField = '2006-08-23 00:23:22'
this seems to work just fine....in the C# code i'm using the date time picker, and formatting it's resulting value to an insert string something like this:
string strSQL = "INSERT INTO MyTable(MyField) VALUES ('" + dateTimePicker1.Value.ToSt
thanks for all your help rytmis! points are yours
sqlite version 3 database (.db3)
the datatype for the db field is "DATETIME" (sqlite admin didn't have this option by default - i typed it in)
finally, the format for the string to insert needs to be: YYYY-MM-DD HH:MM:SS (like u said rytmis)
the reason it wasn't ordering / sorting correctly for me was apparently because of how i formatted my select statement - when it inserts the value, it shows up in the database in this format: 8/23/2006 12:23:22 AM
i assumed my queries should use this same format:
select * from MyTable where MyField = '8/23/2006 12:23:22 AM'
but as it turns out, sqlite wanted it in the same format as the one i used to insert originally (YYYY-MM-DD HH:MM:SS)
select * from MyTable where MyField = '2006-08-23 00:23:22'
this seems to work just fine....in the C# code i'm using the date time picker, and formatting it's resulting value to an insert string something like this:
string strSQL = "INSERT INTO MyTable(MyField) VALUES ('" + dateTimePicker1.Value.ToSt
thanks for all your help rytmis! points are yours
Grazie, much appreciated! :-)
Very glad I could be of service.
Very glad I could be of service.