Postgresql filter results

Question

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

Do you mean an INSERT constraint? Such that only data that passes a certain test is allowed to be inserted into your table? — Cody Caughlan
– Cody Caughlan, Commented Sep 26, 2016 at 16:32
@CodyCaughlan I'll edit the question (including bad code) that I think will clarify — beoliver
– beoliver, Commented Sep 26, 2016 at 16:34
@CodyCaughlan, I've changed the question but the problem will remains. — beoliver
– beoliver, Commented Sep 26, 2016 at 17:07

user330315 · Accepted Answer · 2016-09-26 17:18:05Z

1

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;

answered Sep 26, 2016 at 17:18

user330315

Sign up to request clarification or add additional context in comments.

Comments

James Stonehill · Accepted Answer · 2016-09-26 16:42:25Z

0

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.

answered Sep 26, 2016 at 16:42

James Stonehill

1,2322 gold badges12 silver badges25 bronze badges

2 Comments

beoliver Over a year ago

I would, but the time stamps are 24 hour. The table does however have a "start date"...

user330315 Over a year ago

Note that [start day] is an invalid identifier in SQL. To use an identifier that contains a space you need to use double quotes: e.g. "start day"

Collectives™ on Stack Overflow

Postgresql filter results

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related