CODEWITHSUNDEEP
rubypostgresqlparameters
John Coolidge
John Coolidge

Reputation: 155

How can I use a PSQL timestamp function as a parameter in Ruby?

I am using a Ruby method to access task information from a Postgres database.

def query(statement, *params)
    @logger.info("#{statement}: #{params}")
    @db.exec_params(statement, params)
end

def tasks_completed_for_date(date="CURRENT_DATE")   
    sql = <<~SQL
      SELECT tasks.name AS task_name,
             timetable.start_at AS start_at,
             timetable.end_at AS end_at,
             timetable.duration AS duration,
             timetable.start_at::date AS task_date
        FROM tasks
        JOIN timetable ON tasks.id = timetable.task_id
        WHERE timetable.start_at::date = $1
          AND timetable.duration IS NOT NULL
        ORDER BY tasks.name ASC;
    SQL

    result = query(sql, date)

    if result.ntuples > 0
      result.map do |tuple|
        {
          task_name: tuple['task_name'],
          start_at: tuple['start_at'].slice(11, 5),
          end_at: tuple['end_at'].slice(11, 5),
          total_time: tuple['duration'].slice(0, 5),
          task_date: tuple['task_date'].slice(5, 5)
        }
      end
    end
  end

I want the default value to of the query to be whatever the current date is. I thought the best way to do this was to set the default parameter in the method called tasks_completed_for_date to the string CURRENT_DATE. This string would be interpreted by PSQL as the method CURRENT_DATE and return data based on that date. Instead I receive the following error:

ERROR: date/time value "current" is no longer supported

I am not using 'current' at all and I know that whatever it is, it's obsolete. Is there a way to pass a PSQL method to the query (in place of $1) as I am trying here that doesn't result in an error?

Upvotes: 0

Views: 189

Answers (2)

mechnicov
mechnicov

Reputation: 15298

You can replace $1 with one of these methods:

DATE 'today'
DATE 'now'

Also you can use:

TIMESTAMP 'today'

So for example call method as tasks_completed_for_date('today')

Upvotes: 0

mu is too short
mu is too short

Reputation: 434785

Your problem is that the $1 placeholder's value will be the string 'CURRENT_DATE', not the literal value CURRENT_DATE. The result is that you're effectively saying 'CURRENT_DATE'::date and that doesn't work:

psql> select 'current_date'::date;
ERROR:  date/time value "current" is no longer supported
LINE 1: select 'current_date'::date;
               ^

You need to either get the literal value current_date into the query or find a string that properly casts to a date. The easiest thing would be to use 'now' since that casts nicely:

psql> select 'now'::date;
    date    
------------
 2019-10-22
(1 row)

A quick change to the method's date argument's default value should be good enough:

def tasks_completed_for_date(date = 'now')

Upvotes: 1

Related Questions