Getting a specific timezone in rails sql statement

user1010101 Published at Dev

user1010101

In my Rails application I am executing the following query.

sql = "select A.bus_id as busid, A.stop_id as source, A.arrival as atime, B.arrival as dtime from
(SELECT * from schedules as S where S.stop_id = #{startStopId}) A
inner join
(SELECT * from schedules as S where S.stop_id = #{endStopId}) B
on A.bustag = B.bustag
where A.arrival < B.arrival
and A.arrival > #{Time.now} "

I get the following error

ActiveRecord::StatementInvalid in WelcomeController#create
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15:21:07 +0000' at line 7: select A.bus_id as busid, A.stop_id as source, A.arrival as atime, B.arrival as dtime from (SELECT * from schedules as S where S.stop_id = 25) A inner join (SELECT * from schedules as S where S.stop_id = 1) B on A.bustag = B.bustag where A.arrival < B.arrival and A.arrival > 2015-05-03 15:21:07 +0000

I basically want to do the following

A.arrival > EASTERN STANDARD TIME.

One thing i noticed is that Time.now seems to be giving me UTC time.

So i thought of this solution

#subtract 4 hours to get eastern standard time from UTC.
@time_ = Time.now -0400

 sql = "select A.bus_id as busid, A.stop_id as source, A.arrival as atime, B.arrival as dtime from
    (SELECT * from schedules as S where S.stop_id = #{startStopId}) A
    inner join
    (SELECT * from schedules as S where S.stop_id = #{endStopId}) B
    on A.bustag = B.bustag
    where A.arrival < B.arrival
    and A.arrival > #{@time_} "

I am still getting an error. I am not sure what to do anymore. Appreciate all help.

pangpang

You have to use quotes around #{Time.now}.

For example: "...A.arrival > '#{Time.now}' "

If you want change UTC to EST, you can do like this: