Select rows with time range containing the current time

Question

I have a Postgres table with the following schema:

id | name   | sdate (timestamp)   | edate (timestamp)
---+--------+---------------------+---------------------
1  | test6  | 2013-05-13 01:16:16 | 2013-07-03 10:16:11
2  | test44 | 2012-05-10 10:16:11 | 2013-05-03 00:12:11
3  | test32 | 2013-02-03 00:16:12 | 2013-05-03 00:56:15
4  | test2  | 2013-01-05 08:16:11 | 2014-04-03 10:26:11
6  | test3  | 2013-01-03 00:16:12 | 2013-01-03 04:16:18

I need to return the rows with

select XXX() is in between  sdate and edate

Where XXX() is current utc time + 4:30.000.
In pseudo code:

select count(*)
where ((current utc time  + 4:30.000) >= sdate
    && (current utc time  + 4:30.000) <= edate)

For starters, how do I get the current utc time + 4:30.000 in Postgres?

what's wrong with `current_timestamp between sdate and edate`? — , Jul 02 '13 at 19:08

score 3 · Answer 1 · edited Jul 02 '13 at 20:49

3

select *
from some_table 
where current_timestamp at time zone 'UTC' + interval '4:30' minute BETWEEN sdate and edate;

edited Jul 02 '13 at 20:49

Daniel Vérité

58,074
15
129
156

answered Jul 02 '13 at 19:32

score 1 · Answer 2 · answered Jul 02 '13 at 19:47

You can get current utc time + 4:30.000 in postgres like this

 now() at time zone 'UTC' + interval '4:30.000'
 -- or current_timestamp at time zone 'UTC' + interval '4:30.000'

And your query would be like

SELECT *
FROM aTable
WHERE now() at time zone 'UTC' + interval '4:30.000' >= sdate
AND now() at time zone 'UTC' + interval '4:30.000' <= edate

You can out an SQLFiddle here

score 0 · Answer 3 · edited May 23 '17 at 10:32

Starting with the current Postgres version 9.2 you could use the new range type: tsrange or tstzrange:

CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
  ,t_range tstzrange  -- why tstzrange? see below ..
);

Then you can simply:

SELECT * FROM tbl
WHERE (now() + interval '00:04:30') <@ t_range

Or (see below):

...
WHERE (now() AT TIME ZONE 'UTC' + interval '00:04:30') <@ t_range

<@ .. "element is contained by" operator.

To make this fast, add a GiST index:

CREATE INDEX tbl_t_range_idx ON t_range USING gist (t_range);

Either way, you need to define whether upper and lower border are included or excluded.

->SQLfiddle

"current utc time + 4:30.000"

If you want what the literal value of UTC time right now, transfered and re-interpreted at your local time zone - being a different point in time than "now":

SELECT now() AT TIME ZONE 'UTC'

If you actually want the "current time", vulgo "now", the time zone is irrelevant. There is only one "now". Always and everywhere (with the possible exception of Hollywood movies. Time zones are just different ways of representing that value. The current UTC time is the same as the current eastern standard time or any other current local time. That's also how Postgres operates internally.

If you want "now", just take the local timestamp.

If you want to (or have to) operate with various time zones you should be using timestamptz instead of timestamp to begin with.

More on how Postgres handles time zones in this related answer:
Ignoring timezones altogether in Rails and PostgreSQL

score 0 · Answer 4 · answered Jul 03 '13 at 07:05

0

Just use the correct time zone:

where current_timestamp at time zone 'AFT' BETWEEN sdate and edate;

AFT for Afghanistan and IRDT for Iran

answered Jul 03 '13 at 07:05

Clodoaldo Neto

118,695
26
233
260

Select rows with time range containing the current time

4 Answers4

"current utc time + 4:30.000"