CODEWITHSUNDEEP
sqlpostgresql
aurawastaken
aurawastaken

Reputation: 15

Oddities with postgres SQL [negative date interval and alias that doesn't work only in condition clause]

I'm coming to you guys with with two small oddities I can't seem to understand with postgres:

(1)

  SELECT "LASTREQUESTED", 
         (DATE_TRUNC('seconds', CURRENT_TIMESTAMP - "LASTREQUESTED") 
              - INTERVAL '8 hours') AS "TIME"
    FROM "USER" AS u 
    JOIN "REQUESTLOG" AS r ON u."ID" = r."ID" 
ORDER BY "TIME"

result

I'm calculating when users can make their next request [once every 8 hours], but if you look at entry 16 I get "1 day -06:20:47" instead of "18:00:00" ish, unlike every other line. [The table LASTREQUESTED is a simple timestamp, nothing different here from the other entries for line 16], why is that?

(2)

On the same request, if I try to add a condition on the "TIME" column, the compiler says it doesn't exist although using it to order by is ok. I don't get why.

   SELECT (DATE_TRUNC('seconds', CURRENT_TIMESTAMP - "LASTREQUESTED") 
               - INTERVAL '8 hours') AS "TIME" 
     FROM "USER" AS u 
     JOIN "REQUESTLOG" AS r ON u."ID" = r."ID" 
    WHERE "TIME" > 0 
 ORDER BY "TIME";

Upvotes: 1

Views: 3075

Answers (2)

Dan Getz
Dan Getz

Reputation: 9134

Question #1: negative hours but positive days?

According to the PostgreSQL documentation, this is a situation where PostgreSQL differs from the SQL standard:

According to the SQL standard all fields of an interval value must have the same sign…. PostgreSQL allows the fields to have different signs….

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. …

You can see a more extreme example of this with the following query:

=# select interval '1 day' - interval '300 hours';
     ?column?     
------------------
 1 day -300:00:00
(1 row)

So this is not a single interval in seconds expressed in a strange way; instead, it's an interval of 0 months, +1 day, and -1,080,000.0 seconds. If you are certain that there's no daylight savings time issues with the timestamps that you got these intervals from, you can use justify_hours to convert days into 24-hour periods and get an interval that makes more sense:

=# select justify_hours(interval '1 day' - interval '300 hours');
   justify_hours    
--------------------
 -11 days -12:00:00

Question #2: SELECT columns can't be used in WHERE?

This is standard PostgreSQL behavior. See this duplicate question. Solutions presented there include:

  • Repeat the expression twice, once in the SELECT list, and again in the WHERE clause. (I've done this more times than I want to remember…)

    SELECT (my - big * expression) AS x
FROM stuff
WHERE (my - big * expression) > 5
ORDER BY x

  • Create a subquery without that WHERE filter, and put the WHERE conditions in the outer query

    SELECT *
FROM (SELECT (my - big * expression) AS x
      FROM stuff) AS subquery
WHERE x > 5
ORDER BY x

  • Use a WITH statement to achieve something similar to the subquery trick.

Upvotes: 3

dnoeth
dnoeth

Reputation: 60462

I don't now exactly why it's calculating as-is (maybe because you subtract an Interval from another Interval) but when you change the calculation to Timestamp minus Timestamp it works as expected:

DATE_TRUNC('seconds', CURRENT_TIMESTAMP - (LASTREQUESTED + INTERVAL '8 hours'))

See Fiddle

Regarding #2: Based on Standard SQL the columns in the Select-list are calculated after FROM/WHERE/GROUP BY/HAVING, but before ORDER, that's why you can't use an alias in WHERE. There are some good articles on that topic written by Itzik Ben-Gan (based on MS SQL Server, but similar for PostgreSQL).

Upvotes: 0

Related Questions