Reputation: 1 Apprentice
- Active Posts:
- 34 (0.02 per day)
- 06-March 10
- Profile Views:
- Last Active:
- Mar 15 2013 07:22 PM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Favorite Gaming Platform:
- Your Car:
- Who Cares
- Dream Kudos:
Posts I've Made
Posted 14 Nov 2012Looking at it again, I did notice one minor issue in your query that I didn't spot before. You are doing datetime('now', '- 24 hours'). The space between the - and the 24 hours will cause the function to return nothing, invalidating the entire result set. Try removing it and making it just datetime('now', '-24 hours').
That was it! Shame on me for being sloppy, but thank you so much!
Posted 14 Nov 2012Just for the sake of clarity, I will post my code here (again).
$sth = $db->prepare("SELECT AVG(Power) AS AvgPower, strftime('%H', Timestamp, 'unixepoch', 'localtime') AS TheDate FROM TestTable WHERE datetime(Timestamp, 'unixepoch') >= datetime('now', '- 24 hours') GROUP BY TheDate"); $sth->execute(); $result = $sth->fetchAll(); echo "<pre>"; $counted = count($result); echo "Number of Elements: "."$counted"."<br>"; print_r($result); echo "</pre>";
When I run this, it returns an empty array and a count of 0. I have double checked that I do have data in the table that has a timestamp from within the last 24 hours.
Posted 13 Nov 2012I really set you guys up there
Posted 13 Nov 2012strftime is a PHP function, not an SQL function. What you're doing won't work. If you want to use strftime you need to use it on the resultant data itself (i.e. - once you have retrieved the data, not during).
Well this code will return every entry in my table, in a format equivalent to "datetime":
$sth = $db->prepare("SELECT AVG(Power) AS AvgPower, strftime('%Y-%m-%d %H:%M:%S', Timestamp, 'unixepoch', 'localtime') AS TheDate FROM TestTable GROUP BY TheDate");
And the SQLite Page says, "Notice that all other date and time functions can be expressed in terms of strftime()".
Should I be posting this in the databases forum instead?
Posted 13 Nov 2012Does anyone know what they are going to use it for? I see that it is located at the Oak Ridge National Laboratory...but they seem to do a lot of things there.
- Member Title:
- New D.I.C Head
- Age Unknown
- May 14
- Programming Languages:
- C++, Java, PHP
SimonSays hasn't added any friends yet.