Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton

Joanne, there are no bad questions, only bad answers.

Use sqlite3_bind_double and your timestamp will be of type double (a 64 
bit FP number),


Joanne Pham wrote:

Hi John,
Thanks for the detail info.
I am still very new to SQLite3 and sorry for the question.
My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.
Sorry for the question.
Thanks,
JP


- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 12:59:00 PM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
6. current_time
returns current time as HH:MM:SS
7. current_date
returns current date as -MM-DD
8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


 

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.   http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Dennis Cote

Joanne Pham wrote:

My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.


Joanne,

If you define your table using the "default current_timestamp" clause, 
SQLite will insert the data and time as a string, not as a floating 
point real value.


sqlite> create table t(a int, b real default current_timestamp);
sqlite> insert into t(a) values(1);
sqlite> select a, b, typeof(b) from t;
a   btypeof(b)
--  ---  --
1   2007-12-17 21:56:48  text 
sqlite> insert into t values(2, julianday('now'));

sqlite> select a, b, typeof(b), datetime(b) from t;
a   btypeof(b)   datetime(b)
--  ---  --  ---
1   2007-12-17 21:56:48  text2007-12-17 21:56:48
2   2454452.42202395 real2007-12-17 22:07:42

SQLite calls it's internal floating point type REAL, but the C API 
function used to bind such values is sqlite3_bind_double(), and it binds 
a standard C or C++ double value. Some sample code is shown below. The 
first inserts a julian day number directly, the second uses the 
julianday() function to convert a text string inserted by the code into 
a julian day number in the database.


   sqlite3_stmt* s;
   sqlite3_prepare_v2(db, "insert into t values(:a, :b)", -1, , 0);

   double tomorrow = 2454453.5;
   sqlite3_bind_int(s, 1, 3);
   sqlite3_bind_double(s, 2, tomorrow);

   sqlite3_step(s);
   sqlite3_finalize(s);


   sqlite3_prepare_v2(db, "insert into t values(:a, julianday(:b))", 
-1, , 0);


   char* yesterday = "2007-12-16 00:00:00";
   sqlite3_bind_int(s, 1, 4);
   sqlite3_bind_text(s, 2, yesterday, -1, SQLITE_STATIC);

   sqlite3_step(s);
   sqlite3_finalize(s);


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Hi John,
Thanks for the detail info.
I am still very new to SQLite3 and sorry for the question.
My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.
Sorry for the question.
Thanks,
JP


- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 12:59:00 PM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.

Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
6. current_time
returns current time as HH:MM:SS
7. current_date
returns current date as -MM-DD
8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:
> Thanks John,
> " If you make it a REAL instead of DATETIME your code will 
> be clearer." 
> So you meant that I should make my datatype as REAL instead of DATETIME.
> Thanks,
> Joanne
> 
> 
> 
> 
> - Original Message 
> From: John Stanton <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Monday, December 17, 2007 10:00:11 AM
> Subject: Re: [sqlite] create table with datatype = DATE. SHould use as 
> DATETIME or INTEGER.
> 
> If you declared your date and time (timestamp) column DATETIME it will 
> be floating point and will store date and time in 8 bytes.  Use the FP 
> bind function.  If you make it a REAL instead of DATETIME your code will 
> be clearer.
> 
> Joanne Pham wrote:
>> Hi All,
>> I have two question regarding DATETIME column data type:
>>1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
>> if this column type is INTEGER vs DATETIME then 
>>do the conversion in the GUI code to convert from INTEGER TO DATETIME.
>>
>>2)  And if I store as DATETIME then What is the command to bind this 
>> column type as DATETIME.
>>  For the INTEGER the bind command is : sqlite3_bind_int but I don't know 
>> if the column is DATETIME 
>>then what is the command to bind this column.
>> Thanks,
>> JP
>>
>>
>> - Original Message 
>> From: P Kishor <[EMAIL PROTECTED]>
>> To: sqlite-users@sqlite.org
>> Sent: Thursday, December 13, 2007 2:48:26 PM
>> Subject: Re: [sqlite] create table with datatype = DATE
>>
>> CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)
>>
>> On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
>>> Hi All,
>>> I create the table as :
>>>create table test (name varchar(30), createDate DATE default 
>>> DATETIME('NOW'));
>>> but I got the error message. I want to have the default as now if it is not 
>>> specify.
>>> Thanks in advance,
>>> Joanne
>>>
>>>
>>>  
>>> 
>>> Never miss a thing.  Make Yahoo your home page.
>>> http://www.yahoo.com/r/hs
>>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
>  
> 
> Be a better friend, newshound, and 
> know-it-all with Yahoo! Mobile.  Try it now.   
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton
Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
   2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
   3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
   4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
   5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
   6. current_time
returns current time as HH:MM:SS
   7. current_date
returns current date as -MM-DD
   8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs





-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Dennis Cote

Joanne Pham wrote:

I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
then what is the command to bind this column.
  

Joanne,

You need to familiarize yourself with SQLite's type system. See 
http://www.sqlite.org/datatype3.html for the fundamental data types that 
SQLite supports, and how it handles them.


A column with a declared type of DATETIME, has an column affinity of 
NUMERIC. And such a column can store any of the supported datatypes.


The preferred method of storing time and date information in SQLite is 
using a Julian Day number (see http://en.wikipedia.org/wiki/Julian_Day) 
which is floating point number where the integral part represents the 
date, and the fractional part represents the time. SQLite can use this 
format to efficiently order and compare dates and times in SQL queries.  
You can use the builtin date and time functions (see 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions) to convert 
the stored Julian Day numbers to standard (at least nearly ISO-8601 
standard) date and time strings for display or manipulation by the 
language you are using to drive SQLite.


If you use Julian Day numbers you can declare your timetsamp column REAL 
or FLOAT (or possibly JULIAN or even DATETIME if you wish), and then use 
the sqlite3_bind_double() and sqlite3_column_double() to insert and 
extract the raw floating point values, or sqlite3_column_text() to 
extract a time or date strings returned by the builtin conversion functions.


   create table test (id integer primary key, data text, created julian);

Note you can't use the "default current_timestamp" clause because it 
will insert a string version of the date and time. If you want automatic 
inserts of a Julian Day number you need to use a trigger.


   create trigger set_created after insert on test begin
   update test set created = julianday('now') where rowid = new.rowid;
   end;

If you add an index on the created column you can quickly search for 
dates and times, or date or time ranges.


   create index created_idx on test(created);

You can now easily search for ranges and convert the result for display.

   select id, data from test where created > julianday(date('now', '-30 
days'));


   select id, data, date(created)  from test order by created desc limit 1;

Most of the same things can be accomplished if you store the ISO-8601 
string verisons of the date and time in the database, but that requires 
19 or more bytes per timestamp (and the timestamp value is duplicated in 
each index as well), whereas the Julian Day number requires only 8 bytes 
per timestamp.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.
Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.

Joanne Pham wrote:
> Hi All,
> I have two question regarding DATETIME column data type:
>1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
> if this column type is INTEGER vs DATETIME then 
>do the conversion in the GUI code to convert from INTEGER TO DATETIME.
> 
>2)  And if I store as DATETIME then What is the command to bind this 
> column type as DATETIME.
>  For the INTEGER the bind command is : sqlite3_bind_int but I don't know 
> if the column is DATETIME 
>then what is the command to bind this column.
> Thanks,
> JP
> 
> 
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, December 13, 2007 2:48:26 PM
> Subject: Re: [sqlite] create table with datatype = DATE
> 
> CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)
> 
> On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
>> Hi All,
>> I create the table as :
>>create table test (name varchar(30), createDate DATE default 
>> DATETIME('NOW'));
>> but I got the error message. I want to have the default as now if it is not 
>> specify.
>> Thanks in advance,
>> Joanne
>>
>>
>>  
>> 
>> Never miss a thing.  Make Yahoo your home page.
>> http://www.yahoo.com/r/hs
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton
If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Hi All,
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.

2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if 
the column is DATETIME 
then what is the command to bind this column.
Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
> I create the table as :
>create table test (name varchar(30), createDate DATE default 
> DATETIME('NOW'));
> but I got the error message. I want to have the default as now if it is not 
> specify.
> Thanks in advance,
> Joanne
>
>
>  
> 
> Never miss a thing.  Make Yahoo your home page.
> http://www.yahoo.com/r/hs


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs