postgresql: where/when criteria when selecting timestamp column

Question

Novice to postgresql here. On 8.3 (not my choice, can't do anything about it for near future).

I'm selecting some "time" text strings from a table and inserting them into a view:

create or replace view test as (
    select 
    case 
    when desc like '%opening%' then 'opening'
    when desc like '%closing%' then 'closing'
    else n/a
    end as time_type, 
    to_timestamp(time, 'MM/DD/YYYY HH:MI:SS AM') where time_type = 'closing' as closing_time,
    to_timestamp(time, 'MM/DD/YYYY HH:MI:SS AM') where time_type = 'opening' as opening_time
  from source_table);

I get the error:

ERROR:  syntax error at or near "as"
LINE 8: .../YYYY HH:MI:SS AM') where time_type = 'closing' as closing...

I've used this syntax before to create other views so I am confused. Is it because my where statements are incorrectly placed? But if I place them after the from, they will be applied universally, no, and that is not what I want.

You can only have a single WHERE clause in a SELECT statement. — user330315
– user330315, Commented May 31, 2013 at 20:52

Denis de Bernardy · Accepted Answer · 2013-06-01 07:53:42Z

1

Two expressions look like they should be case statements, e.g.:

case
when time_type = 'closing' then
  to_timestamp(time, 'MM/DD/YYYY HH:MI:SS AM')
else null
end as closing_time

answered Jun 1, 2013 at 7:53

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

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

Comments

twoflower · Accepted Answer · 2013-05-31 20:41:15Z

0

Convert the two projections beginning with to_timestamp to CASE statements as well.

answered May 31, 2013 at 20:41

twoflower

6,8283 gold badges36 silver badges45 bronze badges

Collectives™ on Stack Overflow

postgresql: where/when criteria when selecting timestamp column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related