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

Tim Biegeleisen · Accepted Answer · 2018-03-06T08:59:47.327

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;

edited Mar 06 '18 at 08:59

answered Mar 06 '18 at 08:22

Tim Biegeleisen

502,043
27
286
360

still error ERROR: syntax error at or near "AS" LINE 5: ...MAX(time) < '12:00:00' THEN NULL ELSE MAX(time) AS out – willy andika Mar 06 '18 at 08:26
@willyandika _Don't_ name your columns using `IN`, which is a reserved SQL keyword. – Tim Biegeleisen Mar 06 '18 at 08:27
SELECT col_user_id, col_date, MIN(col_jam) AS masuk, CASE WHEN MAX(col_jam) < '12:00:00' THEN NULL ELSE MAX(col_jam) END AS keluar FROM tb_kehadiran_temp GROUP BY col_user_id, col_date; that my real column name – willy andika Mar 06 '18 at 08:29
1 another question why should i do to insert or update that select into another table? – willy andika Mar 06 '18 at 08:52
You may try `INSERT INTO ... SELECT` syntax. Basically, you can do an insert using the select in my answer, and it will populate another table. – Tim Biegeleisen Mar 06 '18 at 08:53
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166296/discussion-between-willy-andika-and-tim-biegeleisen). – willy andika Mar 06 '18 at 08:54
You should open a new question at this point. What you are asking for has little relation to your original question. – Tim Biegeleisen Mar 06 '18 at 09:28

select 1 row into 2 column query postgresql

1 Answers1