0

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:

WHERE
  "DA-A"."DeliveryHour" = '2:00:00' AND 
  "RT-A"."DeliveryHour" = '2'
1
  • Do you want the two columns to just equal each other or to equal 2 / 2:00:00? Commented Feb 18, 2015 at 4:09

2 Answers 2

0

Referencing Postgres documentation: Date/Time Functions and Operators

How do you like this?

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

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

Or

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 and acknowledge you have read our privacy policy.

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