Re: [sqlite] Need help with the SQL statement.
Thanks a ton Igor!
It worked. Your help is greatly appreciated.
Thanks,
JP
From: Igor Tandetnik
To: sqlite-users@sqlite.org
Sent: Thursday, April 16, 2009 4:52:28 AM
Subject: Re: [sqlite] Need help with the SQL statement.
"Joanne Pham"
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00
In this case, this should work:
strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60
You convert your UTC timestamp to localtime, strip time portion (by way
of date() function), then convert the result back to UTC (by way of
strfrime(..., 'utc'). This way you'll get a UTC timestamp that
corresponds to midnight local time of the same calendar date.
Igor Tandetnik
___
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] Need help with the SQL statement.
"Joanne Pham"
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00
In this case, this should work:
strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60
You convert your UTC timestamp to localtime, strip time portion (by way
of date() function), then convert the result back to UTC (by way of
strfrime(..., 'utc'). This way you'll get a UTC timestamp that
corresponds to midnight local time of the same calendar date.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with the SQL statement.
Hi Igor, 20657220 is number of minutes in GMT time zone. So we need to convert to second by 20657220 *60. select datetime(20657220*60, 'unixepoch','localtime' ); will be 2009-04-11 00:00:00 Thanks for the hlep Igor JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Wednesday, April 15, 2009 9:17:09 PM Subject: Re: [sqlite] Need help with the SQL statement. "Joanne Pham" wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com > But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) > may not be there in the dailyDataTable so min(startTime) won't work > in this case. Any idea Igor? I don't quite see how 20657220 can represent midnight (of any day) when it's not a multiple of 24*60=1440. What epoch are you counting from? This: select datetime(20657220*60, 'unixepoch'); produces 2009-04-11 07:00:00 for me. Normally, I'd expect something like "startTime / 1440 * 1440" to work (this simply rounds down to nearest multiple of 1440). But I guess I don't understand your time representation conventions. Igor Tandetnik ___ 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] Need help with the SQL statement.
On 16/04/2009 2:17 PM, Igor Tandetnik wrote: > "Joanne Pham" > wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com >> But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) >> may not be there in the dailyDataTable so min(startTime) won't work >> in this case. Any idea Igor? > > I don't quite see how 20657220 can represent midnight (of any day) when > it's not a multiple of 24*60=1440. What epoch are you counting from? > This: > > select datetime(20657220*60, 'unixepoch'); > > produces 2009-04-11 07:00:00 for me. > > Normally, I'd expect something like "startTime / 1440 * 1440" to work > (this simply rounds down to nearest multiple of 1440). But I guess I > don't understand your time representation conventions. Message headers: Date: Wed, 15 Apr 2009 20:24:38 -0700 (PDT) ^ From: Joanne Pham 12 AM in (e.g.) San Francisco is 7 AM UTC HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with the SQL statement.
"Joanne Pham" wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com > But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) > may not be there in the dailyDataTable so min(startTime) won't work > in this case. Any idea Igor? I don't quite see how 20657220 can represent midnight (of any day) when it's not a multiple of 24*60=1440. What epoch are you counting from? This: select datetime(20657220*60, 'unixepoch'); produces 2009-04-11 07:00:00 for me. Normally, I'd expect something like "startTime / 1440 * 1440" to work (this simply rounds down to nearest multiple of 1440). But I guess I don't understand your time representation conventions. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with the SQL statement.
Thanks a lot for respond my email!
But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) may not be
there in the dailyDataTable so min(startTime) won't work in this case.
Any idea Igor?
Thanks
JP
From: Igor Tandetnik
To: sqlite-users@sqlite.org
Sent: Wednesday, April 15, 2009 7:44:48 PM
Subject: Re: [sqlite] Need help with the SQL statement.
"Joanne Pham"
wrote in message news:594788.4966...@web90305.mail.mud.yahoo.com
> Hi All,
> I have the following table(dailyDataTable) as defined below
> startTime INTEGER : number of minutes in GMT time
> appId INTEGER : application Id
> remoteId INTEGER : server id
> proxyCount INTEGER
> This table can have up to 24 hours as below: (this table can have
> only few rows but it can have up to 24 hours).
> startTime appId remoteId proxyCount
> 20657220 1 2 101 -- this is 2009-04-11 00:00:00
> 20657280 1 2 105 -- this is 2009-04-11 01:00:00|
> ...
> 20658540 1 2 101 -- this is 2009-04-11 22:00:00
> 20658600 1 2 105 -- this is 2009-04-11 23:00:00
>
> I need to take these data and insert into another
> table(weeklyDataTable) with the following sql statement:
> 1) login to weeklyDB
> 2) Run the following sql statement
> ATTACH DATABASE 'dailyDB' as DailyDB; insert into weeklyDataTable
> select (strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId,
> remoteId, sum(proxyCount ) from DailyDB.dailyDataTable group by
> appId, remoteId ; DETACH DATABASE DailyDB; "
>
> Result below in weeklyDataTable
> 20656800 1 2 2386| -- this 2009-04-10 17:00:00
> But this is wrong I want to have this row below in the
> weeklyDataTable as below.
> 20657220 1 2 2386| -- this 2009-04-11 00:00:00
Perhaps something like this:
insert into weeklyDataTable
select min(startTime), appId, remoteId, sum(proxyCount )
from DailyDB.dailyDataTable
group by appId, remoteId;
Igor Tandetnik
___
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] Need help with the SQL statement.
"Joanne Pham"
wrote in message news:594788.4966...@web90305.mail.mud.yahoo.com
> Hi All,
> I have the following table(dailyDataTable) as defined below
> startTime INTEGER : number of minutes in GMT time
> appId INTEGER : application Id
> remoteId INTEGER : server id
> proxyCount INTEGER
> This table can have up to 24 hours as below: (this table can have
> only few rows but it can have up to 24 hours).
> startTime appId remoteId proxyCount
> 20657220 1 2 101 -- this is 2009-04-11 00:00:00
> 20657280 1 2 105 -- this is 2009-04-11 01:00:00|
> ...
> 20658540 1 2 101 -- this is 2009-04-11 22:00:00
> 20658600 1 2 105 -- this is 2009-04-11 23:00:00
>
> I need to take these data and insert into another
> table(weeklyDataTable) with the following sql statement:
> 1) login to weeklyDB
> 2) Run the following sql statement
> ATTACH DATABASE 'dailyDB' as DailyDB; insert into weeklyDataTable
> select (strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId,
> remoteId, sum(proxyCount ) from DailyDB.dailyDataTable group by
> appId, remoteId ; DETACH DATABASE DailyDB; "
>
> Result below in weeklyDataTable
> 20656800 1 2 2386| -- this 2009-04-10 17:00:00
> But this is wrong I want to have this row below in the
> weeklyDataTable as below.
> 20657220 1 2 2386| -- this 2009-04-11 00:00:00
Perhaps something like this:
insert into weeklyDataTable
select min(startTime), appId, remoteId, sum(proxyCount )
from DailyDB.dailyDataTable
group by appId, remoteId;
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need help with the SQL statement.
Hi All,
I have the following table(dailyDataTable) as defined below
startTime INTEGER : number of minutes in GMT time
appId INTEGER : application Id
remoteId INTEGER : server id
proxyCount INTEGER
This table can have up to 24 hours as below: (this table can have only few rows
but it can have up to 24 hours).
startTime appId remoteId proxyCount
20657220 1 2 101
-- this is 2009-04-11 00:00:00
20657280 1 2 105
-- this is 2009-04-11 01:00:00|
...
20658540 12 101
-- this is 2009-04-11 22:00:00
20658600 12 105
-- this is 2009-04-11 23:00:00
I need to take these data and insert into another table(weeklyDataTable) with
the following sql statement:
1) login to weeklyDB
2) Run the following sql statement
ATTACH DATABASE 'dailyDB' as DailyDB; insert into weeklyDataTable select
(strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId, remoteId,
sum(proxyCount ) from DailyDB.dailyDataTable group by appId, remoteId ; DETACH
DATABASE DailyDB; "
Result below in weeklyDataTable
20656800 1 2 2386| -- this 2009-04-10 17:00:00
But this is wrong I want to have this row below in the weeklyDataTable as below.
20657220 1 2 2386| -- this 2009-04-11 00:00:00
Basically I want to have one row in the weeklyDataTable which have the 00:00:00
for hourly part.
Please help. Your help is greatly appreciate.
Thanks in advance,
JP
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
