Page 1 of 1

PHP create data between dates

Posted: Tue Dec 21, 2010 9:36 am
by mrlayance
Not sure if this is possible but here goes. This is my query:

Code: Select all

SELECT `time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d') as `newtime3` FROM ipport WHERE FROM_UNIXTIME(`time`, '%Y-%m-%d') >= '2010-11-26' AND FROM_UNIXTIME(`time`, '%Y-%m-%d') <= '2010-11-30' AND (`protocal` = 'Echo' ) AND `div` = 'XDIV'
Results are as follows:

Code: Select all

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2010-11-28"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2010-11-29"
Is it possible to include days that do not exist and give them the value 0? Even null would work. Not sure if some type of range command exists.
Desired results Example:

Code: Select all

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"0";"Echo";"0";"0";"XDIV";"2010-11-26"
"0";"Echo";"0";"0";"XDIV";"2010-11-27"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2010-11-28"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2010-11-29"
"0";"Echo";"0";"0";"XDIV";"2010-11-30"
Thanks :banghead:

Re: PHP create data between dates

Posted: Wed Dec 22, 2010 6:36 am
by mrlayance
I hate doing this, but does anyone have any ideas?

Re: PHP create data between dates

Posted: Thu Dec 23, 2010 12:04 pm
by novice4eva
I thought this would be simple but umm if you can find a query that selects dates between two ranges then you could use that sql but right now all I can think of is creating a temporary table with just one column lets say DATES of type date in which you will insert dates from some min date to max date(using a plsql).
Then you could do an outer join on date.