select 1 row into 2 column query postgresql

Question

I have table like this

user_id |          date |        time|
"1"     |   "2017-01-03"|   "06:59:35"
"1"     |   "2017-01-03"|   "07:01:17"
"1"     |   "2017-01-03"|   "12:03:21"
"1"     |   "2017-01-03"|   "16:06:14"
"2"     |   "2017-01-03"|   "07:10:52"
"2"     |   "2017-01-03"|   "07:11:38"
"2"     |   "2017-01-03"|   "07:12:04"
"3"     |   "2017-01-03"|   "07:12:06"
"3"     |   "2017-01-03"|   "09:12:33"
"3"     |   "2017-01-03"|   "16:13:29"

This is my mysql query

SELECT  
    col_user_id as user_id, 
    col_date as tanggal, 
    if(MAKETIME(HOUR(MIN(col_jam),MINUTE(MIN(col_jam),00) <= '12:00', MAKETIME(HOUR(MIN(col_jam),MINUTE(MIN(col_jam),00), NULL) as jam_masuk, 
    if(MAKETIME(HOUR(MAX(col_jam),MINUTE(MAX(col_jam),00) > '12:00', MAKETIME(HOUR(MAX(col_jam),MINUTE(MAX(col_jam),00), NULL) as jam_keluar 
    FROM tb_kehadiran_temp
    WHERE 
    col_date >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH
    AND 
    col_date < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
    GROUP BY col_user_id,col_date

I want my result like this on postgre query:

user_id | date       | in       | out 
1       | 2017-01-03 | 06:59:35 | 16:06:14 
2       | 2017-01-03 | 07:10:52 | null 
3       | 2017-01-03 | 07:12:04 | 16:13:29

and i want to move to other table with condition insert or update if data is exists

Answer 1

I think a simple GROUP BY query should work here, assuming your time column is an actual time type, or if it's text, it is fixed width:

SELECT
    user_id,
    date,
    MIN(time) AS "in",   -- DON'T use in to name a column; it's a keyword
    CASE WHEN MAX(time) < '12:00:00' THEN NULL ELSE MAX(time) END AS out
FROM tb_kehadiran_temp
GROUP BY
    user_id,
    date;

Edit:

If you needed to populate another table based on the results of the above query, you could try using INSERT INTO ... SELECT syntax, e.g.

INSERT INTO otherTable (user_id, date, min_time, max_time)
SELECT
    user_id,
    date,
    MIN(time) AS "in",
    CASE WHEN MAX(time) < '12:00:00' THEN NULL ELSE MAX(time) END AS out
FROM tb_kehadiran_temp
GROUP BY
    user_id,
    date;