I am getting an error when pushing a Rails 3 app into production on Heroku (postgres) while locally (sqlite) the code works fine - I took at look at the Postgres manual to no avail:
My Code:
(strftime('%s','now') - strftime('%s', posts.created_at)
Error:
LINE 1: SELECT posts.*, ((strftime('%s','now') -...
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
In PostgreSQL you don't have a function strftime. To compute the number of seconds between to dates you can use:
SELECT extract(epoch from now() - posts.created_at);
It's also important to remark that in PostgreSQL you have the interval data type, so if you just subtract (now() - posts.created_at) it will return a nice interval type for you, which can be easily formated with to_char.
What exactly is that function supposed to do?
To format a date or timestamp column use to_char()
To calculate the difference in days between two dates simply use: now() - posts.created_at (assuming created_at is a column of type date)
If created_at is not a date column (which would be a big mistake) you need to use to_date() to convert a string to a date.
created_at should be a timestamp.Between formatting and postgres' date/time types and functions you need to work out the exact syntax.
If you are aiming for portability between sqlite and postgres a possible solution is to define strftime function in postgres.
