Rafi Cohen wrote:
Hi, I apologize in advance for the probably very basic question as I'm a
newbie to sqlite and integrating sql in C applications.
One of my tables in my database requires a date column. I understand
from a tutorial not related to C/C++ api that insering date columns is
not trivial and triggers need to be used.
As I am not knowledgeable about triggers, I would like to ask how can I
insert a date column in a C application and later retrieve this column
or compare it with current date in a select statement?
I need to insert just any date in this column and not only the current
date.
If somebody can send me an example off list to implement this, I'll much
appreciate it.
Thanks, Rafi.
Rafi,
SQLite does not support SQL standard date and time types. It does
however have a very flexible set of date and time functions that use
strings (i.e. -MM-DD and/or HH:MM:SS etc), integer numbers (i.e.
unix epoch times, seconds since 1970-01-01), or floating point numbers
(i.e julian day numbers) which can all be stored in the database. These
function also allow these formats to be easily converted from one type
to another, in particular, from integer to string or floating point to
string for display. These numeric formats make date comparisons very
simple. For more information see
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Once you have decided how you want to store your dates you can insert
them easily using a default value for your column or supplying a date in
the required format. Lets assume you decide to store strings since they
are human readable, even if somewhat inefficient from a storage point of
view.
create table t (id, data, created default current_date);
insert into t (id, data) values (1, 'test');
insert into t (id, data) values (2, 'more');
select created from t;
2007-02-28
2007-02-28
You can also provide an explicit value that overrides the default value
when you add a record.
insert into t values (3, 'explicit', '2007-02-14');
insert into t values (3, 'explicit', date('now', '+2 days'));
And you can update these dates using the date and time functions
update t set created = date(created, '-1 month') where id = 2;
Finally you can use the date and time functions to convert the dates for
display.
select created, julianday(created) from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
