Count all records from midnight until now()

Question

I'm trying to grab all records created from midnight of the current day until the time a button is pressed.

I've tried something like this meanwhile but it doesn't really solve the issue.

SELECT COUNT(*) FROM ... AND a.lastAction > now() - interval '24 hour'"

What I need is something like:

SELECT COUNT(*) FROM ... AND a.lastAction > midnight() AND a.lastAction < now()

Is there a way to convert a string like "2013-08-06 00:00:01" to a timestamp?

Answer 1

lastAction between current_date and now()   

select '2013-08-06 00:00:01'::timestamp;
      timestamp      
---------------------
 2013-08-06 00:00:01

Date arithmetic is quite simple in Postgresql. It is possible to sum or subtract any fraction of an interval like 1.5 hours

select '2013-08-06 00:00:01'::timestamp + 1.5 * interval '1 hour';
      ?column?       
---------------------
 2013-08-06 01:30:01