CODEWITHSUNDEEP
sqlruby-on-railsruby
Karmacy
Karmacy

Reputation: 1

Searching timestamp with query but ignoring the time

geniuses!

so in my project i'm trying to use a query to search my single-table-database by timestamp, which is formatted like this: "2018-05-01 00:28:43". In my request i would like to ignore the time and display all the results which have the chosen date as a timestamp.

Now i'm still learning, tried a lot of searching but could not come up with an answer. This is where i'm at:

def index
  if params[:query].present?
    @reports = Report.where(created_at: params[:query])
  else
    @reports = Report.all
  end
end

Upvotes: 0

Views: 190

Answers (4)

John Baker
John Baker

Reputation: 2398

You can try this:

def index
  if params[:query].present?
    date = Date.parse(params[:query])
    @reports = Report.where(created_at: date.midnight..date.end_of_day)
  else
    @reports = Report.all
  end
end

Upvotes: 0

Catherine Zarra
Catherine Zarra

Reputation: 21

I would do something like this:

def index
  if params[:query].present?
    # gives a date without a time 
    date = Date.parse(params[:query])
    @reports = Report.where(created_at: date..(date + 1.day)) 
  else
    @reports = Report.all
  end
end

Upvotes: 1

Bartosz Pietraszko
Bartosz Pietraszko

Reputation: 1407

Use SQL date() function.

def index
  @date = Date.parse(params[:query]) rescue nil
  if @date
    @reports = Report.where("date(created_at) = ?", @date)
  else
    @reports = Report.all
  end
end

Do not pass params[:query] directly as an argument for where clause, parse it to Date object first to make sure that it's actually a date.

Upvotes: 0

Sidonai
Sidonai

Reputation: 3696

You can try something like this:

def index
  if params[:query].present?
    @reports = Report.where(created_at: (Time.new(params[:query]).beginning_of_day()..Time.new(params[:query]).end_of_day()).all
  else
    @reports = Report.all
  end
end

If you need to format the results you can parse the column using all.map( ) like this

Report.where(created_at: (Time.new(params[:query]).beginning_of_day()..Time.new(params[:query]).end_of_day()).all.map{|report| report.created_at = Time.new(report.created_at).your_format }

Upvotes: 0

Related Questions