beoliver beoliver - 2 months ago 21
SQL Question

Postgresql filter results

Assumeing I have a table

Foo


id | startDate | startTime | endTime | name


I am trying to find occurrences that "pass midnight"...
start < 00:00:00
and
end > 00:00:00


I can use the following to get the times

select extract (epoch from (endTime - startTime)) as time from Foo


but how can I add a constraint that allows me to filter the return values for only those < 0 (which should be those satisfying the "midnight" property)

I tried

select extract (epoch from (endTime - startTime)) as timeSpent from Foo where timeSpent < 0
ERROR: column "timeSpent" does not exist

Answer

You can't reference an alias on the same level where you define it. You need to wrap that in a derived table:

select * 
from (
  select extract (epoch from (endTime - startTime)) as timeSpent 
  from Foo 
) t 
where timespent < 0;
Comments