Syntax Error in Datetime range Query in Ruby on Rails

Question

I am executing a ruby query:

Timeslot.where("start_date >= ? AND end_date <= ?", Wed, 01 Jul 2015 18:00:00 SGT +08:00, Wed, 01 Jul 2015 21:00:00 SGT +08:00)

I am getting the following error

 SyntaxError: unexpected tCONSTANT, expecting ')'
...AND end_date <= ?", Wed, 01 Jul 2015 18:00:00 SGT +08:00, We...

I suppose it is because I am not escaping the ',' character after Wed. I have two questions:

1) How can I correct this query? 2) I will be replacing Wed, 01 Jul 2015 18:00:00 SGT +08:00 with an actual datetime variable? How can I type this query in the right way if the datetime were variables and not constants?

start_time= Time.now and end_time = Time.now + 2.hours. Query: Timeslot.where("start_date >= ? AND end_date <= ?", start_time, end_time) — Pardeep Dhingra
– Pardeep Dhingra, Commented Jul 13, 2015 at 10:20
Wed, 01 Jul 2015 18:00:00 SGT +08:00 is neither a valid Ruby date, nor a valid SQL date. — Stefan
– Stefan, Commented Jul 13, 2015 at 10:56
@Stefan Yes it is. In the default created_at field of rails models, this is the format you are going to find — gazubi
– gazubi, Commented Jul 13, 2015 at 11:05
Exactly, it's a format, i.e. a string representation, not the date itself. Entering Date.today in IRB (or rails console) returns a date that is formatted as Mon, 13 Jul 2015, but entering Mon, 13 Jul 2015 results in a syntax error. — Stefan
– Stefan, Commented Jul 13, 2015 at 11:17

spickermann · Accepted Answer · 2015-07-13 10:55:52Z

1

I would write something like this:

Timeslot.where(
  'start_date >= ? AND end_date <= ?', 
  Date.parse('Wed, 01 Jul 2015 18:00:00 SGT +08:00'),
  Date.parse('Wed, 01 Jul 2015 21:00:00 SGT +08:00')
)

answered Jul 13, 2015 at 10:55

spickermann

108k9 gold badges115 silver badges147 bronze badges

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

Comments

rick · Accepted Answer · 2015-07-13 10:25:04Z

0

It seem that you have to convert string date into in the proper date format to perform query operation. Like you have date in UTC time zone, then its better to use Time object and convert into proper date format and use that in your query,

str = "Tue, 10 Aug 2010 01:20:19 +0400"
puts Date.parse str
2010-08-10

puts Date.parse(Time.parse(str).utc.to_s)
2010-08-09

answered Jul 13, 2015 at 10:25

rick

1,7053 gold badges15 silver badges28 bronze badges

Comments

myggul · Accepted Answer · 2015-07-13 10:31:34Z

0

Did you input parameters Wed, 01 Jul 2015 18:00:00 SGT +08:00 without quotation marks?

I think if you want to use that format. Try this.

"Wed, 01 Jul 2015 18:00:00 SGT +08:00".to_time()
# to_time() method converted to Time Class (timezone is followed by rails setting)

or

"2015-07-01 18:00:00".to_time() # works well.

answered Jul 13, 2015 at 10:31

myggul

3871 gold badge5 silver badges23 bronze badges

Comments

Milind · Accepted Answer · 2015-07-13 10:57:58Z

0

USE DATETIME ...

start_date=DateTime.parse(Wed, 01 Jul 2015 18:00:00 SGT +08:00)
end_date=DateTime.parse(Wed, 01 Jul 2015 21:00:00 SGT +08:00)
Timeslot.where("start_date >= ? AND end_date <= ?",start_date,end_date)

answered Jul 13, 2015 at 10:57

Milind

5,1222 gold badges29 silver badges58 bronze badges

Collectives™ on Stack Overflow

Syntax Error in Datetime range Query in Ruby on Rails

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related