Re: [sqlite] Default Column Value to Local Time
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
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
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
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
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
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
[sqlite] Default Column Value to Local Time
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? 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
