Hi Denis, thank you very much for the briliant explanation and the refference you pointed. This clearly explains my options here. Just another small question to complete this subject: in case of prepared insert statements, assuming the date is stored as string as in your example, do I use sqlite3_bind_text with a variable pointing to a string containing the date I want to insert? In other words, is this a string just like any other string I insert into the table? Thanks, Rafi.
-----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 5:28 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to insert and select a date column 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. YYYY-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] ------------------------------------------------------------------------ ----- -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.4/705 - Release Date: 2/27/2007 3:24 PM ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
