Help translating Rails Sqlite query to postgresql

Question

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")

Using sqlite for development and postgres for deployment is asking for trouble. Why not develop on postgres? — Denis de Bernardy
– Denis de Bernardy, Commented Aug 1, 2011 at 19:44
@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
– NicSlim, Commented Aug 1, 2011 at 19:51

Jeff Paquette · Accepted Answer · 2011-08-01 19:50:18Z

4

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")

answered Aug 1, 2011 at 19:50

Jeff Paquette

7,1472 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NicSlim Over a year ago

Thank you! I was a little unclear about how to use those functions.

standingwave · Accepted Answer · 2011-08-01 19:51:17Z

1

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.

answered Aug 1, 2011 at 19:51

standingwave

4883 silver badges8 bronze badges

1 Comment

NicSlim Over a year ago

Yes. I plan to start running Postgres locally, although getting that up and running is causing trouble for me too :/

Sameer C · Accepted Answer · 2011-08-01 19:56:04Z

1

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")

answered Aug 1, 2011 at 19:56

Sameer C

2,7971 gold badge18 silver badges12 bronze badges

1 Comment

NicSlim Over a year ago

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.

Collectives™ on Stack Overflow

Help translating Rails Sqlite query to postgresql

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related