And I should also add...sqlite doesn't respect timezones for past/future dates.
So...you may want to consider storing both UTC and localtime and then you can
do whatever you want by selecting the appropriate field. That way historical
reports can be accurate with localtime.
Or you can store the UTC offset and just add it back in when you need it to do
the same thing in a smaller data field.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Wednesday, June 27, 2012 11:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite time is 2 hours to late
Kinda' depend on what exactly you want to do.
If you want your application to always use local time no matter where it's run:
select('now','localtime');
If you want to know how far off of UTC you are do this:
select round((julianday('now','localtime')-julianday('now'))*24);
In my case this returns -5 for Central Daylight Time.
If your case you should 2 hours.
If the time has any meaning across timezones where your applicaiton is run you
should stick with UTC and perhaps just display local time to the users.
sqlite> select datetime('now');
2012-06-27 16:14:46
sqlite> create table t(mydate);
sqlite> insert into t values(datetime('now'));
sqlite> select datetime(mydate,'localtime') from t;
2012-06-27 11:17:13
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 27, 2012 10:45 AM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite time is 2 hours to late
Am 27.06.2012 17:40, schrieb Jay A. Kreibich:
>
> On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on
> the wall:
>
>> Hello,
>>
>>> sqlite3 event.db "select datetime('now')";
>> gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
>> than my system time ( win 7 ) 17::33:13
>>
>> How can this be fixed ?
> Move two timezones to the west.
>
> (By default all times and dates are UTC.)
>
> -j
>
>
I use this from within a c++ application
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
")";
How do I get the right time in the the column eventdate ?
How to move timezones ?
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
