CODEWITHSUNDEEP
postgresqltimestampintervals
user4874888
user4874888

Reputation:

Postegres: Queries with INTERVAL operation don work over constraints

Just in matter of simplification, i have the following query:

SELECT * FROM table WHERE (DATE_TRUNC('day',create_time ) > now() - interval '2 days');

Running explain i got this:

->  Seq Scan on table  (cost=0.00..1.62 rows=10 width=232)
               Filter: (date_trunc('day'::text, create_time) > (now() - '2 days'::**interval**))

As I've highlighted, this operation (now() - interval '2 days') returns an interval, but i need it to be a timestamp.

In this case, how can i convert from interval to timestamp or some thing like that ?

Thank you all for answers, but I think it is not very well explained, here's the detailed problem:

For performance purposes, we have here a table named 'transactions' and child tables for each day, for example 'transactions_2015_05_29'.

On each child table we have the following constraint:

CONSTRAINT transactions_2015_05_29_create_time_check CHECK (date_trunc('day'::text, create_time) = '2015-05-29 00:00:00'::timestamp without time zone)

When we run the following 'explain' the following query we get this:

Explain:

explain SELECT * FROM pp_transactions WHERE (DATE_TRUNC('day', create_row_time) < current_date + interval '1 day');

"Result  (cost=0.00..120.52 rows=731 width=232)"
"  ->  Append  (cost=0.00..120.52 rows=731 width=232)"
"        ->  Seq Scan on transactions  (cost=0.00..1.70 rows=10 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < (('now'::text)::date + '1 day'::interval))"
"        ->  Seq Scan on **transactions_2015_05_28** pp_transactions  (cost=0.00..14.65 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < '2015-05-30 00:00:00'::timestamp without time zone)"
"        ->  Seq Scan on **transactions_2015_05_29** transactions  (cost=0.00..16.98 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < (('now'::text)::date + '1 day'::interval))"
"        ->  Seq Scan on **transactions_2015_05_30** transactions  (cost=0.00..16.98 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < (('now'::text)::date + '1 day'::interval))"
"        ->  Seq Scan on **transactions_2015_05_31** transactions  (cost=0.00..16.98 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < (('now'::text)::date + '1 day'::interval))"

As you can see, there are tables that should not be there. But if we run the following query i get the correct result on explain:

Explain:

explain select * FROM pp_transactions WHERE (DATE_TRUNC('day', create_row_time) < '2015-05-30 00:00:00');

"Result  (cost=0.00..30.76 rows=216 width=232)"
"  ->  Append  (cost=0.00..30.76 rows=216 width=232)"
"        ->  Seq Scan on transactions  (cost=0.00..1.46 rows=10 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < '2015-05-30 00:00:00'::timestamp without time zone)"
"        ->  Seq Scan on **transactions_2015_05_28** pp_transactions  (cost=0.00..14.65 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < '2015-05-30 00:00:00'::timestamp without time zone)"
"        ->  Seq Scan on **transactions_2015_05_29** pp_transactions  (cost=0.00..14.65 rows=103 width=232)"
"              Filter: (date_trunc('day'::text, create_time) < '2015-05-30 00:00:00'::timestamp without time zone)"

So, the first query must behave as the second one.

Upvotes: 1

Views: 157

Answers (2)

Meno Hochschild
Meno Hochschild

Reputation: 44061

Theoretical reasoning why there is no official conversion offered by DB-vendors (related to the title of your question):

The only way for the conversion of such incompatible types is introducing a fixed (but arbitrary) start timestamp, add your interval (better to say duration) on it and determine the final timestamp which is then your result. Note that due to timezone effects (daylight saving) the conversion can be dependent on the choice of the starting timestamp because a day is not always 24 hours long.

Maybe it is healthier to abandon such a strange conversion however. It is like comparing apples and oranges. Or in geometric interpretation: How do you convert a distance to a point?

For the concrete analysis of your PostgreSQL-statement please also look at the PostgreSQL-documentation. I am wondering why you think that you get an interval as result of now() - interval '2 days'. Can you please show your observed output? But maybe you have not observed your output but just been confused about the text of SQL-EXPLAIN where the text "interval" in the expression (now() - '2 days'::**interval**) is related to the preceding part "2 days", not to the whole expression.

Upvotes: 0

SQL.injection
SQL.injection

Reputation: 2647

from the postgres manual:

  1. You may add or subtract an INTERVAL to a TIMESTAMP to produce another TIMESTAMP

TIMESTAMP '1999-12-11' + INTERVAL '19 days' = TIMESTAMP '1999-12-30'

And this looks like a date to me (2 days ago at 19:08 CEST), and according to the manual (once again) now() produces a timestamp:

 (now() - '2 days'::**interval**)

now() is a function and '2 days' an interval.

Upvotes: 1

Related Questions