CODEWITHSUNDEEP
sqlpostgresqldate-range
Kajal
Kajal

Reputation: 223

Select rows with time range containing the current time

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?

Upvotes: 0

Views: 1349

Answers (4)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

Just use the correct time zone:

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

AFT for Afghanistan and IRDT for Iran

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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

Upvotes: 0

user330315
user330315

Reputation: 

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

Upvotes: 3

Java Devil
Java Devil

Reputation: 10959

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

Upvotes: 1

Related Questions