CODEWITHSUNDEEP
postgresql
Arun Killu
Arun Killu

Reputation: 14233

Ignoring seconds from timestamp postgres

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 .

Upvotes: 0

Views: 1646

Answers (2)

Vlad Bezden
Vlad Bezden

Reputation: 89537

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;

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions