Saving dates in SQLite?

Question

Hello, I've found this from this link:

http://www.sqlite.org/datatype3.html

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.date and time functions.

I'm a bit confused as to which I should use. I'm creating a Windows Forms C# application and will be using the built DateTime control.

Which option would best suit me? I think TEXT would, but maybe I'm mistaken. I'm new to SQLite.

Thank you.

Answer 1

I have been using Text and haven't had any problem with it.

Answer 2

I assume you are using System.Data.SQLite? Even if SQLite itself doesn't recognize datetime data type, this data provider recognizes it.

For example, if you use SQLiteDataAdapter to populate a datatable with a select statement, if the data type of a column is datetime, the returned datacolumn will be datetime.

There is limitation to the provider: it can't guess data type when your select contains more than one table. In this case, you can declare the returning data type yourself by prepending your query like this:

types [integer], [text], [boolean], [datetime];
select A.id, A.subject, B.isactive, B.due_date from ...

The provider stores the data as text like 2009-04-01 17:42:38.828125. SQLite is fine with this format, for example you can calculate the next day with:

select datetime('2009-04-01 17:42:38.828125', '+1 days');

EDIT: you specify data type as datetime like this:

create table C ( d datetime );