Derive a new column based on Time stamp

Question

I have a column 'customer_date' with datatype text.

Based on the time i need to derive a new column

Condition :

when customer_date <12 then 'AM' else 'PM

customer_date

01Mar2018 10:03:54
02Mar2018 13:03:54

expected output

  customer_date             session

01Mar2018 10:03:54        AM
02Mar2018 13:03:54        PM

It throws me error when i wrote

select 
case EXTRACT(HOUR FROM customer_date)<12 then 'AM' else 'PM' end as session
from my table;

According to your [previous question](https://stackoverflow.com/q/52102740/593144) you translating `timstamp` value to `text` and then trying to work with `text` value as with `timstamp`? IMO it is silly a bit. — Abelisto, Aug 30 '18 at 21:47

score 2 · Accepted Answer · answered Aug 30 '18 at 20:27

2

This works when I test it:

select (case when EXTRACT(HOUR FROM customer_date::timestamp) < 12 then 'AM' else 'PM' end) as session
from mytable;

I added the explicit conversion and the when.

answered Aug 30 '18 at 20:27

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Aug 30 '18 at 20:48

You forget add WHEN in the CASE WHEN as @Gordon Linoff say.

There is a simple way, using TO_CHAR instead of CASE WHEN.

use to TO_CHAR

AM, am, PM or pm will get meridiem indicator (without periods)

CREATE TABLE myTable(customer_Date timestamp);

insert into myTable values ('01Mar2018 10:03:54'::timestamp);

insert into myTable values ('02Mar2018 13:03:54'::timestamp);

Query 1:

select customer_Date,TO_CHAR(customer_Date::timestamp, 'AM') as session
from mytable

Results:

|        customer_date | session |
|----------------------|---------|
| 2018-03-01T10:03:54Z |      AM |
| 2018-03-02T13:03:54Z |      PM |

Derive a new column based on Time stamp

2 Answers2