04-04-2010 12:36 PM - edited 04-04-2010 01:25 PM
SQLite doesn't have a DATE type. You have three options for storing dates in SQLite:
See the SQLite data types documentation for how to deal with dates in SQL queries, including lots of functions for manipulating SQLite date values.
For Java Date or Calendar objects, the easiest approach, I think, is to use Date.getTime/setTime to convert between Date and long values.
Of course, if you need to deal with dates before 1970, you need to use one of the other representations. [misinformation removed following Peter's posting below]
04-04-2010 12:37 PM - edited 04-04-2010 12:38 PM
04-04-2010 12:46 PM
Note that setting a Date to a negative value does actually represent a date before 1/1/1970. For example, setting the date to -1000, is one second to midnight on 31 December 1969.
04-04-2010 01:24 PM
Peter's right about negative integers for dates. An 8-byte integer (a Java long value) can go back well before the start of the known universe, "and so suffices" (to quote the Wikipedia article on Unix time ).
04-16-2010 05:31 PM
Just a quick follow-up about using negative integers for Date objects. It works in Java and in SQLite (because it doesn't really know about dates). But if you interact with a server that is using MySQL, this could bite you. MySQL treats Unix epoch numbers as unsigned integers. Trying to convert a negative epoch value to a MySQL timestamp or date/time value doesn't work to well in MySQL. Caveat emptor.
(I knew that I hadn't just imagined that pre-1970 problem.)