Re: [sqlite] sqlite time is 2 hours to late
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
Re: [sqlite] sqlite time is 2 hours to late
> UTC is "the right time." If you're doing anything with dates and > times I would STRONGLY recommend that all recorded times are in UTC. Jay is right. I've been bitten by storing local times before. Even if your users are in the same time zone, that time zone shifts with day light savings. It was a painful lesson. Always store times in UTC. Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] sqlite time is 2 hours to late
Fully agree with you, Jay.
-> SQLite NUL "select datetime('now','localtime');"
E.
On Wed, Jun 27, 2012, at 10:55, Jay A. Kreibich wrote:
> On Wed, Jun 27, 2012 at 05:45:41PM +0200, deltagam...@gmx.net scratched on
> the wall:
> > 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.)
>
>
> > 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 ?
>
> UTC is "the right time." If you're doing anything with dates and
> times I would STRONGLY recommend that all recorded times are in UTC.
> Anything online and anything mobile tends to be used from different
> timezones.
>
> As for converting to the local time for display purposes, see:
>
> http://sqlite.org/lang_datefunc.html
>
> In specific, the "localtime" modifier.
>
> > How to move timezones ?
>
> Car, usually.
>
>
>-j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> 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] sqlite time is 2 hours to late
On Wed, Jun 27, 2012 at 05:45:41PM +0200, deltagam...@gmx.net scratched on the
wall:
> 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.)
> 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 ?
UTC is "the right time." If you're doing anything with dates and
times I would STRONGLY recommend that all recorded times are in UTC.
Anything online and anything mobile tends to be used from different
timezones.
As for converting to the local time for display purposes, see:
http://sqlite.org/lang_datefunc.html
In specific, the "localtime" modifier.
> How to move timezones ?
Car, usually.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] sqlite time is 2 hours to late
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
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite time is 2 hours to late
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 ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
