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
