How to set timezone for SQL?

Question

I'm using SQLite for my cgi application, but the timezone I'm in is different from that of the shared server this stuff is running on. Can I set my timezone somehow in SQLite so I don't need to mess up my application with time conversions and I can use things like NOW() in my SQL? If not: what are my other options?

Answer 1

SQLite doesn't have a way to set the connection time zone because it's targeted towards embedded database uses--whatever you use to change your C library localtime() output should work for SQLite. This works for me on MacOS:

$ TZ=America/New_York sqlite3 dbfile "select datetime('now','localtime');"
2010-01-06 09:53:28

$ TZ=America/Los_Angeles sqlite3 dbfile "select datetime('now','localtime');"
2010-01-06 06:53:41