Left Join with date and timestamp columns in postgreSQL

Question

I have two tables

table1 (id, dept_id, position_id, pin, name, last_name, create_time)

table2 (dept_id, pin, name, last_name, zone_id, create_time)

and they have both create_time column which stores date and time in this format (2019-12-01 18:00:00.568) this column very important I need to match the dates and time to make Left join Table1 to Table2 I can make a match with the date but with time I have a problem, the time in both columns always have the difference in second or in milliseconds so I try to convert to text with to_char() I need to join two tables daily and hourly manner (24 hours) to make the report

Here is my query

SELECT p.dept_id, p.pin, p.name, p.last_name, p.position_id, a.zone_id, p.create_time::date, to_char(p.create_time::timestamp::time, 'HH24:MI') as time
FROM table1 p
LEFT JOIN table2 a
ON p.create_id::date=a.create_time::date AND
p.pin=a.pin AND p.dept_id=a.dept_id

It gives me date matches but doesn't provide time matches. I need something like this

SELECT p.dept_id, p.pin, p.name, p.last_name, p.position_id, a.zone_id, p.create_time::date, to_char(p.create_time::timestamp::time, 'HH24:MI') as time1, to_char(a.create_time::timestamp::time, 'HH24:MI') as time2
    FROM table1 p
    LEFT JOIN table2 a
    ON p.create_id::date=a.create_time::date AND
    time1 = time2 AND 
    p.pin=a.pin AND 
    p.dept_id=a.dept_id

Nick · Accepted Answer · 2020-05-09 01:40:21Z

1

You can use date_trunc to truncate the timestamp to the hour and join on that:

SELECT p.dept_id, p.pin, p.name, p.last_name, p.position_id, a.zone_id,
       p.create_time::date, to_char(p.create_time::timestamp::time, 'HH24:MI') as time
FROM table1 p
LEFT JOIN table2 a ON date_trunc('hour', p.create_time) = date_trunc('hour', a.create_time)
                  AND p.pin=a.pin
                  AND p.dept_id=a.dept_id

edited May 9, 2020 at 1:40

answered May 9, 2020 at 1:24

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-05-09 01:19:30Z

0

You can use date/time inequalities. For instance:

SELECT p.dept_id, p.pin, p.name, p.last_name, p.position_id, a.zone_id, 
       p.create_time::date, to_char(p.create_time::timestamp::time, 'HH24:MI') as time
FROM table1 p LEFT JOIN
     table2 a
     ON p.pin = a.pin AND
        p.dept_id = a.dept_id AND
        p.create_id >= a.create_time - INTERVAL '5 second' AND
        p.create_id <= a.create_time + INTERVAL '5 second';

This logic is for them being within 5 seconds on either side.

answered May 9, 2020 at 1:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Left Join with date and timestamp columns in postgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related