Re: [sqlite] How to add a column with CURRENT_DATETIME?
On 5/28/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > It seems docs/compile.html is wrong. > > > > SQLITE_OMIT_DATETIME_FUNCS > > If this option is defined, SQLite's built-in date and time manipulation > > functions are omitted. Specifically, the SQL functions julianday(), date(), > > time(), datetime() and strftime() are not available. The default column > > values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available. > > > > So it should be CURRENT_TIMESTAMP instead of CURRENT_DATETIME. > > > > > You should file a documentation bug report to get this corrected. > > It is an unfortunate choice of names, for the very reason that caused > this error. > Yes indeed. > I would really like to see this changed. I my opinion there should be at > least four such default values. Three that return the date and/or time > strings used now, and a fourth that returns a floating point julianday > number that would be stored in the database directly. > >Name Inserts >=== >CURRENT_DATE string date >CURRENT_TIME string time >CURRENT_DATETIME string date and time >CURRENT_TIMESTAMPreal julianday number > > Another possibility would be to use the TIMESTAMP name for an integer > unix epoch timestamp, and JULIANDAY for the floating point julian day > number, giving five default value codes. > >Name Inserts >=== >CURRENT_DATE string date >CURRENT_TIME string time >CURRENT_DATETIME string date and time >CURRENT_TIMESTAMPinteger unix timestamp >CURRENT_JULIANDAYreal julianday number > > I realize this will break backwards compatibility but perhaps this could > be considered for the upcoming 3.6 series. This change would provide for > smaller database files, since the integer and real formats are normally > smaller than the string they encode, and faster default inserts, since > they don't require executing the time and date string formating code. Lovely idea. All that remains is convincing DRH. I would also like to see the datetime functions documentation moved from the wiki to the main syntax pages... perhaps under expressions. Being out there in the wiki makes it seem like an afterthought hack. > > > Dennis Cote > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a column with CURRENT_DATETIME?
On Wed, May 28, 2008 at 1:31 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > Another possibility would be to use the TIMESTAMP name for an integer > unix epoch timestamp, and JULIANDAY for the floating point julian day > number, giving five default value codes. > > Name Inserts > === > CURRENT_DATE string date > CURRENT_TIME string time > CURRENT_DATETIME string date and time > CURRENT_TIMESTAMPinteger unix timestamp > CURRENT_JULIANDAYreal julianday number How about CURRENT_EPOCH for unix timestamp? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a column with CURRENT_DATETIME?
[EMAIL PROTECTED] wrote: > It seems docs/compile.html is wrong. > > SQLITE_OMIT_DATETIME_FUNCS > If this option is defined, SQLite's built-in date and time manipulation > functions are omitted. Specifically, the SQL functions julianday(), date(), > time(), datetime() and strftime() are not available. The default column > values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available. > > So it should be CURRENT_TIMESTAMP instead of CURRENT_DATETIME. > You should file a documentation bug report to get this corrected. It is an unfortunate choice of names, for the very reason that caused this error. I would really like to see this changed. I my opinion there should be at least four such default values. Three that return the date and/or time strings used now, and a fourth that returns a floating point julianday number that would be stored in the database directly. Name Inserts === CURRENT_DATE string date CURRENT_TIME string time CURRENT_DATETIME string date and time CURRENT_TIMESTAMPreal julianday number Another possibility would be to use the TIMESTAMP name for an integer unix epoch timestamp, and JULIANDAY for the floating point julian day number, giving five default value codes. Name Inserts === CURRENT_DATE string date CURRENT_TIME string time CURRENT_DATETIME string date and time CURRENT_TIMESTAMPinteger unix timestamp CURRENT_JULIANDAYreal julianday number I realize this will break backwards compatibility but perhaps this could be considered for the upcoming 3.6 series. This change would provide for smaller database files, since the integer and real formats are normally smaller than the string they encode, and faster default inserts, since they don't require executing the time and date string formating code. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a column with CURRENT_DATETIME?
It seems docs/compile.html is wrong.
SQLITE_OMIT_DATETIME_FUNCS
If this option is defined, SQLite's built-in date and time manipulation
functions are omitted. Specifically, the SQL functions julianday(), date(),
time(), datetime() and strftime() are not available. The default column
values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available.
So it should be CURRENT_TIMESTAMP instead of CURRENT_DATETIME.
Thanks.
--
From: "P Kishor" <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2008 12:54 AM
To: "General Discussion of SQLite Database"
Subject: Re: [sqlite] How to add a column with CURRENT_DATETIME?
> On 5/28/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> I am trying to create a table with a column with default value of current
>> datetime (on insertion). But the following sql script can pass, but does
>> not do what it is supposed to do.
>>
>> What is the correct syntax?
>>
>> create table ViewState
>> (
>> session_id text not null,
>> viewstate_key text not null,
>> viewstate blob,
>> timestamp text default CURRENT_DATETIME
>> )
>> ;
>
>
> sqlite> CREATE TABLE foo (i INTEGER PRIMARY KEY, t TEXT, d DATETIME
> DEFAULT CURRENT_TIMESTAMP);
> sqlite> INSERT INTO foo (t) VALUES ('junk');
> sqlite> SELECT * FROM foo;
> 1|junk|2008-05-28 16:53:42
> sqlite>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a column with CURRENT_DATETIME?
[EMAIL PROTECTED] wrote: > I am trying to create a table with a column with default value of current > datetime (on insertion). But the following sql script can pass, but does not > do what it is supposed to do. > > What is the correct syntax? > > create table ViewState > ( > session_id text not null, > viewstate_key text not null, > viewstate blob, > timestamp text default CURRENT_DATETIME > ) > ; > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > You need to use CURRENT_TIMESTAMP as shown at http://www.sqlite.org/lang_createtable.html HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to add a column with CURRENT_DATETIME?
On 5/28/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I am trying to create a table with a column with default value of current
> datetime (on insertion). But the following sql script can pass, but does not
> do what it is supposed to do.
>
> What is the correct syntax?
>
> create table ViewState
> (
> session_id text not null,
> viewstate_key text not null,
> viewstate blob,
> timestamp text default CURRENT_DATETIME
> )
> ;
sqlite> CREATE TABLE foo (i INTEGER PRIMARY KEY, t TEXT, d DATETIME
DEFAULT CURRENT_TIMESTAMP);
sqlite> INSERT INTO foo (t) VALUES ('junk');
sqlite> SELECT * FROM foo;
1|junk|2008-05-28 16:53:42
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to add a column with CURRENT_DATETIME?
I am trying to create a table with a column with default value of current datetime (on insertion). But the following sql script can pass, but does not do what it is supposed to do. What is the correct syntax? create table ViewState ( session_id text not null, viewstate_key text not null, viewstate blob, timestamp text default CURRENT_DATETIME ) ; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
