I am running a cron job every 1 minute for notifying users for events
select * from events where event_start = now() - interval '30 minutes'
so that I can send the users a notification prior to 30 mins of event
problem is event start is a timestamp field so if there is a difference in seconds it this wll not work ,so how can ignore the seconds part .
You can use date_trunc() to remove the seconds:
select *
from events
where event_start = date_trunc('second', now()) - interval '30' minutes
More details in the manual: http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
In order to ignore seconds, you can use date_trunc() function.
The function date_trunc is conceptually similar to the trunc function for numbers.
date_trunc(field, source [, time_zone ]) source is a value expression of type timestamp, timestamp with time zone, or interval. (Values of type date and time are cast automatically to timestamp or interval, respectively.) field selects to which precision to truncate the input value. The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).
Valid values for field are:
- microseconds
- milliseconds
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- decade
- century
- millennium
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00
So in your case, you should use:
SELECT *
FROM events
WHERE event_start = date_trunc('minute', now()) - INTERVAL '30' MINUTE;
