|
Using FROM_UNIXTIME on Epoch Dates
By: Bruce Bahlmann - Contributing Author (your
feedback
is important to us!)
In rails development, you may have an occasion where you need to search Epoch
data stored within your database - or even combine multiple Epoch date
fields during a conditional statement in order to filter your returned
results.
Rails leverages Epoch functionality that already exists within your database
(SQL interface language) to generate clean looking date comparisons. The following example shows the
syntax of these calls using the latest rails (3.1.1) chaining format.
[Adding columns containing Epoch dates from two different tables]
@inventories = Inventory.joins(:warranty).
where("FROM_UNIXTIME((purchaseDate + global_warranty.tkey),'%Y') = #{params["year"]}").
where(:classification_id => params["class"])
In the above example, we first join the :warranty table with the main
(Inventory) table. Next we add the two Epoch date columns from two different
tables together and convert the total from Epoch (Unix Time) to a normal
date format. In this case, only the year (%Y) is compared so the query
results in all the entries with having a total Epoch time that has a
particular year associated with it. Note that the above statement will
perform 1+N queries due to the need to look up individual classifications.
[Eager loading version of above query]
@inventories = Inventory.joins(:warranty).
where("FROM_UNIXTIME((purchaseDate + global_warranty.tkey),'%Y') = #{params["year"]}").
where(:classification_id => params["class"]).
includes(:classification,:warranty)
The addition to this query is simply the includes statement which enables
the query to cache the connecting table and therefore forego the need to
conduct individual queries to populate classification and warranty data.
Can Birds-Eye.Net help you or your Company?
Receive your Birds-Eye.Net articles and white
papers hot off
the presses by adding our RSS feed to your reader.
|
|
|
(C) Copyright Birds-Eye.Net, All rights reserved.
It is against the law to reproduce this content or any portion of it in any form without the explicit written permission of Birds-Eye Network Services, LLC. Federal copyright law (17 USC 504) makes it illegal, punishable with fines up to $100,000 per violation plus attorney's fees.
|