CODEWITHSUNDEEP
sqlpostgresqlbetween
deimos0
deimos0

Reputation: 23

Creating view with additional column based on multiple condition

I've been trying to sort out some data from my current job. I need to create additional column with shift number that depends on the product creation hour. The table looks like this:

product_id    creation_time         warehouse_name
  varchar       timestamp                varchar
    1      2016-03-29 08:30:45            11
    2      2016-03-15 14:15:10            9
    3      2016-03-14 05:40:45            11

Now I need to add a column with a shift number that's based on the hours - 06:00:00 - 14:00:00 shift 1, 14:00:00-22:00:00 shift 2 and 22:00:00-06:00:00 shift 3. I'm quite new when it comes to sql, I know how to add a column via AS, but I don't know how to fill the column with correct data based on the condition that's based on the timestamps. We're using postgres database via pgadmin.I tried something like:

 SELECT creation_time, product_id, warehouse_name
 FROM products WHERE creation_time BETWEEN LIKE '%06:00:00' AND '%14:00:00';

but I'm lost at how to insert the condition data into a new column named shifts.

Upvotes: 2

Views: 1114

Answers (1)

user330315
user330315

Reputation:

You shouldn't compare timestamp values using LIKE. To select the rows with a time between 06:00 and 14:00 cast the timestamp to a time and compare that:

SELECT creation_time, product_id, warehouse_name
FROM products 
WHERE creation_time::time BETWEEN time '06:00:00' AND time '14:00:00';

The same "trick" can be used to create the shift column: 

SELECT creation_time, product_id, warehouse_name, 
       case 
         when creation_time::time between time '06:00:00' AND time '14:00:00' then 1
         when creation_time::time BETWEEN time '14:00:01' AND time '22:00:00' then 2
         else 3
       end as shift_number
FROM products;

The between operator includes both values that's why the second check starts 1 second after 14:00

time '06:00:00' specifies a time literal (constant). For more details on how to specify time (or timestamp) values, please see the manual:

http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT

Upvotes: 1

Related Questions