title: Left Join with date and timestamp columns in postgreSQL
body: <p>I have two tables</p>
  
  <p>table1 (id, dept_id, position_id, pin, name, last_name, create_time)<br></p>
  
  <p>table2 (dept_id, pin, name, last_name, zone_id, create_time)<br></p>
  
  <p>and they have both <strong>create_time</strong> column which stores date and time in this format <strong><em>(2019-12-01 18:00:00.568)</em></strong>
  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 <br>
  <hr>
  Here is my query</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>It gives me date matches but doesn't provide time matches. I need something like this</p>
  
  <pre><code>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 
  </code></pre>