CODEWITHSUNDEEP
sqlpostgresql
beoliver
beoliver

Reputation: 5759

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

Upvotes: 0

Views: 395

Answers (2)

user330315
user330315

Reputation:

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;

Upvotes: 1

James Stonehill
James Stonehill

Reputation: 1212

What I would think about doing is creating a sub-query that isolates the part of the date that denotes the day, and writing a statement that roughly equates to:

WHERE [start day] != [end day]

This will enable you to extract a specific date from the table.

Upvotes: 0

Related Questions