Alexey Pechnikov wrote: > > and compare is right, but is this correct way for date values? >
Yes, this is correct, or at least expected, for date values stored as floating point julian day numbers. The binary floating point values used are the best approximations to the decimal rational numbers you entered on the command line, but they are not always exactly the same. To see the difference, you can subtract one value from the other. Since save_date is less than the entered value, subtracting it from the entered value should give a small positive difference. select 2454612.21079943 - save_date from photo_tags where save_date<2454612.21079943 limit 1 offset 3073; To do comparisons with date values such as these that contain small roundoff and truncation errors, you must decide to what resolution you need the dates to be the same before you consider them equal. If you consider dates to be equal when the two dates are the same to within one second, then you could use that value as your maximum difference. Since a julian day number has units of days, you can use a value of 1/86400 days (i.e. 1 second) as your epsilon value. The same value can be used to test for less than as you are doing in your query. The save_date is less than the specified value if the difference is greater than this epsilon value. select save_date from photo_tags where 2454612.21079943 - save_date > 1/86400; Another, less efficient but perhaps more readable way to do these comparisons is to use the data and time functions to convert the floating point values to strings which can be compared directly. The julian day number of the limit can be converted to a string once before the query is executed. select save_date from photo_tags where datetime(save_date) < '2008-05-25 17:03:33'; HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
