Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I use the following code to help find sales for the current month. My development db is SQLite, but I'm using Heroku for production, which uses PostgreSQL. So I am getting an strftime function doesn't exist error. How would I translate this, and is there a way to translate it so that it still works when querying my dev SQLite db as well? 

sales = current_user.location.sales.find( :all,
                                          :conditions => ["strftime('%Y', date) = '?'
                                                          AND strftime('%m', date) = ?",
                                                          Date.today.year,
                                                          Date.today.strftime('%m')],
                                                          :order => "date")
share|improve this question
 
which version of Rails? –  kain Aug 1 '11 at 19:41
2  
Using sqlite for development and postgres for deployment is asking for trouble. Why not develop on postgres? –  Denis Aug 1 '11 at 19:44
 
@kain using Rails 3.0 –  NicSlim Aug 1 '11 at 19:49
 
@Denis I am currently working on installing postgres to develop on. I just started on sqlite because I'm pretty new and didn't know that Heroku was using postgres. –  NicSlim Aug 1 '11 at 19:51
add comment

3 Answers

up vote 4 down vote accepted

Postgres does not support strftime. You can find a list of supported date/time functions here.

sales = current_user.location.sales.find( :all,
                                          :conditions => ["extract(year from date) = '?'
                                                          AND extract(month from date) = ?",
                                                          Date.today.year,
                                                          Date.today.strftime('%m')],
                                                          :order => "date")
share|improve this answer
 
Thank you! I was a little unclear about how to use those functions. –  NicSlim Aug 1 '11 at 19:56
add comment
up vote 1 down vote

I think to_timestamp or to_char are the analogous Postgres functions you want to take a look at. The docs are pretty straightforward; basically similar to strftime with different template patterns: Postgres Datatype Formatting Functions

Maybe more than you're interested in, but I would really recommend running Postgres locally if you're doing a lot of work with Heroku. It will save you quite a bit of pain down the road.

share|improve this answer
 
Yes. I plan to start running Postgres locally, although getting that up and running is causing trouble for me too :/ –  NicSlim Aug 1 '11 at 19:58
add comment
up vote 1 down vote

If you are trying to get the sales figures for the current month, this might be a more appropriate, database agnostic query.

current_user.location.sales.where(:date => (Time.now.beginning_of_month..Time.now)).order("date")

share|improve this answer
 
Thanks. This works well too, but caused a different issue for me when I try to calculate the sum of each sales column and the sales variable is empty. I ended up going with the postgres functions and switching my dev db to postgres. –  NicSlim Aug 1 '11 at 21:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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