MySQL join on lowest range integer from related table
Posted: Thu Feb 04, 2010 1:21 pm
Hi everyone
I've got a table of exchange rates and another table of invoices.
4 columns in exrates: exrate_id, exrate_eur, exrate_usd, exrate_stamp
4 columns in invoices (for this example): invoice_id, amount, currency, invoice_stamp
exrate_stamp and invoice_stamp are both integers which store a unix timestamp (not mysql timestamp).
The exrate_stamp is only the year/month... so converting exrate_stamp to a formatted time would return 1st Monthname 2010 00:00
invoice_stamp is the year/month/day... so this could translate as 17th Monthname 2010 00:00
Basically what I want to do is list out my invoices but also grabbing the exchange rate that was applicable for that invoice's year/month from my exrates table.
So if I have an invoice with an invoice_stamp of 24th November 2006, I'd like to do a join on exrates which would return the exchange rate for November 2006 along with the invoice details.
I can't do a plain left join between invoice_stamp and exrate_stamp because the 2 will rarely match... the invoice would have to have an invoice_stamp of 1st November 2006 00:00 to match the value of exrate_stamp
So I thought about using MySQL's ceiling() function in a join but that won't work as all my timestamps are integers.
I also thought about converting the invoice_stamp using FROM_UNIXTIME() but formatting it to only return 2006-11, then converting that 2006-11 back to a unix stamp using UNIX_TIMESTAMP() and doing the join on that value but I'm concerned what MySQL will do with timezones... according to the manual this conversion won't be lossless.
I manage all timezone and date formatting stuff in PHP not in MySQL.
Also that seems like a bit of a fudged way of doing things - a whole bunch of date formatting by the MySQL server just to perform a join.
And the 3rd way I thought of doing it was by using FROM_UNIXTIME() on exrate_stamp and invoice_stamp but having it formatted to only return 2006-11. But that means doing a MySQL join between 2 string values and I thought that was generally a bad thing.
Anyone got any suggestions or comments of a better "mathmatical" way of doing this?
Or is everyone totally confused about my database schema explanations above?
Cheers, B
I've got a table of exchange rates and another table of invoices.
4 columns in exrates: exrate_id, exrate_eur, exrate_usd, exrate_stamp
4 columns in invoices (for this example): invoice_id, amount, currency, invoice_stamp
exrate_stamp and invoice_stamp are both integers which store a unix timestamp (not mysql timestamp).
The exrate_stamp is only the year/month... so converting exrate_stamp to a formatted time would return 1st Monthname 2010 00:00
invoice_stamp is the year/month/day... so this could translate as 17th Monthname 2010 00:00
Basically what I want to do is list out my invoices but also grabbing the exchange rate that was applicable for that invoice's year/month from my exrates table.
So if I have an invoice with an invoice_stamp of 24th November 2006, I'd like to do a join on exrates which would return the exchange rate for November 2006 along with the invoice details.
I can't do a plain left join between invoice_stamp and exrate_stamp because the 2 will rarely match... the invoice would have to have an invoice_stamp of 1st November 2006 00:00 to match the value of exrate_stamp
So I thought about using MySQL's ceiling() function in a join but that won't work as all my timestamps are integers.
I also thought about converting the invoice_stamp using FROM_UNIXTIME() but formatting it to only return 2006-11, then converting that 2006-11 back to a unix stamp using UNIX_TIMESTAMP() and doing the join on that value but I'm concerned what MySQL will do with timezones... according to the manual this conversion won't be lossless.
I manage all timezone and date formatting stuff in PHP not in MySQL.
Also that seems like a bit of a fudged way of doing things - a whole bunch of date formatting by the MySQL server just to perform a join.
And the 3rd way I thought of doing it was by using FROM_UNIXTIME() on exrate_stamp and invoice_stamp but having it formatted to only return 2006-11. But that means doing a MySQL join between 2 string values and I thought that was generally a bad thing.
Anyone got any suggestions or comments of a better "mathmatical" way of doing this?
Or is everyone totally confused about my database schema explanations above?
Cheers, B