Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:29 PM, Derrell Lipman <
[EMAIL PROTECTED]> wrote:

>
>
> On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:
>
>>
>> Okay, I give up. I've been searching for a while now. I've found a number
>> of
>> discussions about how CURRENT_DATE returns the date in UTC, and that it
>> can
>> be converted to local time. But I've yet to find one article that clearly
>> states how one might do this.
>>
>> I would love to have a table column default to the current date, but I
>> want
>> it to be the date where the computer is located, rather than some far off
>> place.
>>
>> Can anyone tell me if this is possible?
>
>
> I think this is the page you're looking for:
>   http://www.sqlite.org/lang_datefunc.html
>
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
>
> Note, however, that if sqlite is being accessed by your web server,
> 'localtime' is in reference to the web server's time zone, not the time zone
> of the web browser which could be anyplace in the world.  If your
> application is running all locally, the above should work.
>

I guess I didn't quite complete the picture here.  I don't think you can use
functions as default values, but you can easily use triggers to accomplish
the same thing, like this:

sqlite> CREATE TABLE x (i INTEGER PRIMARY KEY, t TIMESTAMP);
sqlite> CREATE TRIGGER x_insert_tr
   ...>   AFTER INSERT ON X
   ...>   FOR EACH ROW
   ...>   BEGIN
   ...> UPDATE x
   ...>   SET t  = datetime('now', 'localtime')
   ...>WHERE i = new.i;
   ...>   END;
sqlite> INSERT INTO x (i) VALUES (1);
sqlite> INSERT INTO x (i) VALUES (2);
sqlite> INSERT INTO x (i) VALUES (3);
sqlite> SELECT * FROM x;
1|2008-09-01 23:32:49
2|2008-09-01 23:32:55
3|2008-09-01 23:33:01
sqlite>

Those times shown above are when I ran the INSERT queries, in my local time
zone.

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread P Kishor
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>
>  P Kishor-3 wrote:
>  >
>  > Don't think it is possible. As you noted, the docs say very clearly
>  > (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
>  > constraint specifies a default value to use when doing an INSERT. ..
>  > default value may also be one of the special case-independant keywords
>  > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
>  > CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
>  > date and/or time is inserted into the columns. "
>  >
>
>
> Thanks for your input. This is really a shame, and quite surprising, that it
>  works this way with no means to modify the behavior. There are all sorts of
>  flags to modify how the source code works, but I could find nothing related
>  to this. Based on my previous searches, I am not the only one who thinks it
>  should work this way. It should at least be an option.

well, maybe. How data are stored is/should_be irrelevant to the end
user. In fact, the best way to store dates might well be just numbers
(search the archives for discussions on how to best store
dates/times). The key is in retrieving and displaying (or doing
whatever one wants to do with them) the data.

You have all the options in the world to use the datetime functions
for that, or, even more so in your application. All you have to do is
to remember how your data were inserted in the first place.


>
>  Thanks.
>
> --
>  View this message in context: 
> http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.html
>
> Sent from the SQLite mailing list archive at Nabble.com.
>
>  ___
>  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] Default Column Value to Local Time

2008-09-01 Thread jonwood


P Kishor-3 wrote:
> 
> Don't think it is possible. As you noted, the docs say very clearly
> (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
> constraint specifies a default value to use when doing an INSERT. ..
> default value may also be one of the special case-independant keywords
> CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
> CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
> date and/or time is inserted into the columns. "
> 

Thanks for your input. This is really a shame, and quite surprising, that it
works this way with no means to modify the behavior. There are all sorts of
flags to modify how the source code works, but I could find nothing related
to this. Based on my previous searches, I am not the only one who thinks it
should work this way. It should at least be an option.

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263371.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread jonwood


Derrell Lipman wrote:
> 
> I think this is the page you're looking for:
>   http://www.sqlite.org/lang_datefunc.html
> 
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
> 

Well, okay. That's one of the articles I did see. Right now, I'm having two
issues:

1. I'm not sure how to use those functions. You've gave me some examples,
which I appreciate. But your examples don't work with columns. Perhaps the
first argument needs to be my column name?

2. I'm currently using a default value of CURRENT_DATE in several tables. At
this stage, it would be a lot less work if I could continue to not explictly
specify the initial value for these columns. Can anyone confirm that this is
simply not possible to do this with the local time? (I want the value
actually stored in the database to be local time, and not to simply convert
the UTC value every time it is used.)

Thanks!

-- 
View this message in context: 
http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263339.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread P Kishor
On 9/1/08, jonwood <[EMAIL PROTECTED]> wrote:
>
>  Okay, I give up. I've been searching for a while now. I've found a number of
>  discussions about how CURRENT_DATE returns the date in UTC, and that it can
>  be converted to local time. But I've yet to find one article that clearly
>  states how one might do this.
>
>  I would love to have a table column default to the current date, but I want
>  it to be the date where the computer is located, rather than some far off
>  place.
>
>  Can anyone tell me if this is possible?

Don't think it is possible. As you noted, the docs say very clearly
(http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT
constraint specifies a default value to use when doing an INSERT. ..
default value may also be one of the special case-independant keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. ... If the value is
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC
date and/or time is inserted into the columns. "

So, you can store only UTC date/time, but you can always convert on
retrieving the values by using datetime('now', 'localtime')

One thing to note -- the UTC date/time doesn't seem to account for
daylight savings adjustment. For example, I am right now in Washington
DC where it is 10:30 PM. It is 3:30 AM in the UK right now, but per
SQLite (the UTC bit), it is 2:30 AM. I guess I have to do all this
adjustment in my queries and application.


>
>  Thanks.
>
> --
>  View this message in context: 
> http://www.nabble.com/Default-Column-Value-to-Local-Time-tp19263208p19263208.html
>  Sent from the SQLite mailing list archive at Nabble.com.
>
>  ___
>  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] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:

>
> Okay, I give up. I've been searching for a while now. I've found a number
> of
> discussions about how CURRENT_DATE returns the date in UTC, and that it can
> be converted to local time. But I've yet to find one article that clearly
> states how one might do this.
>
> I would love to have a table column default to the current date, but I want
> it to be the date where the computer is located, rather than some far off
> place.
>
> Can anyone tell me if this is possible?


I think this is the page you're looking for:
  http://www.sqlite.org/lang_datefunc.html

In particular, a query that returns the current time in the local (to
sqlite) time zone is:
   SELECT datetime('now', 'localtime');
or for just the date:
   SELECT date('now', 'localtime');

Note, however, that if sqlite is being accessed by your web server,
'localtime' is in reference to the web server's time zone, not the time zone
of the web browser which could be anyplace in the world.  If your
application is running all locally, the above should work.

Cheers,

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users