Looking for advice on how to match two differing column types:

I have two tables: RT-A and DA-B.

Under both tables there is a column named DeliveryDate, but the individual tables use differing column types; one time without timezone and the other numeric.

Example: in the DA-A table the DeliveryDate column reads: "02:00:00" Example: in the RT-A table the DeliveryDate column reads, "2".

What is the correct expression in the WHERE clause of an SQL statement to join rows where they equal each other? Plain equality obviously does not work With the current design:

"DA-A"."DeliveryHour" = "RT-A"."DeliveryHour"

This is how I have been writing the SQL, but it seems to be terrible in form:

  "DA-A"."DeliveryHour" = '2:00:00' AND 
  "RT-A"."DeliveryHour" = '2'
  • Do you want the two columns to just equal each other or to equal 2 / 2:00:00? – Erwin Brandstetter Feb 18 '15 at 4:09

Referencing Postgres documentation: Date/Time Functions and Operators

How do you like this?

WHERE date_part('hour', "DA-A"."DeliveryHour") = "RT-A"."DeliveryHour"
  • 2
    date_part() is the Postgres function that implements the standard SQL extract(). Same thing. – Erwin Brandstetter Feb 18 '15 at 7:47
  • @DW: This is what I was looking for. Thanks! – JAS Feb 19 '15 at 2:31
  • To join all rows that "equal" each other:

WHERE "DA-A"."DeliveryHour"
    = '0:0'::time + "RT-A"."DeliveryHour" * interval '1h'


WHERE EXTRACT(hour FROM "DA-A"."DeliveryHour")::numeric
    = "RT-A"."DeliveryHour"

One or the other may be favorable to keep one side sargable when you want to use an index in a query on one or the other side.

  • For the case where you are only interested in 2 / 2:00:00, your explicit expressions may be the best choice, since both expressions are sargable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.