CODEWITHSUNDEEP
postgresql
Yeshky
Yeshky

Reputation: 25

How to create a CASE WHEN by checking the time of a column

I have a column named "create_date" in a table which has datetime mentioned in UTC.

Actual:

|create_date|

2019-07-30 14:52:38.0  
2019-07-29 09:49:51.0  
2019-07-30 04:27:56.0  
2019-08-02 16:07:44.0  
2019-08-05 02:30:12.0  
2019-08-03 20:44:14.0

I would like create another column using CASE WHEN with condition as time between '02:30:00.0' and '14:30:00.0' as "India" and ELSE as "Not India"

Expected:

|create_date|City|

2019-07-30 14:52:38.0 | Not India  
2019-07-29 09:49:51.0 | India  
2019-07-30 04:27:56.0 | India  
2019-08-02 16:07:44.0 | Not India  
2019-08-05 02:30:12.0 | India  
2019-08-03 20:44:14.0 | Not India

I've tried executing the below query but had no luck

SELECT create_date,      
CASE WHEN (to_char(create_date, 'HH:MI:SS') BETWEEN '02:30:00' AND '14:30:00') THEN    'India'  
ELSE 'Not India'  
END AS "City"  
FROM TABLE_NAME

Upvotes: 1

Views: 373

Answers (3)

谢宇恒
谢宇恒

Reputation: 1

SELECT create_date,      
CASE WHEN (to_char(create_date, 'HH24:MI:SS') BETWEEN '02:30:00' AND '14:30:00') THEN    'India'  
ELSE 'Not India'  
END AS "City"  
FROM TABLE_NAME

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

Try using the ::time cast:

SELECT
    create_date,
    CASE WHEN create_date::time BETWEEN '02:30:00.0' AND '14:30:00.0'
         THEN 'India' ELSE 'Not India' END AS City
FROM TABLE_NAME;

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246188

You need to use the HH24 format string rather than HH to get the hours in 24-hour clock format.

Upvotes: 0

Related Questions