Difference between postgresql time and rails 3 app time

Question

Scenario: I want to get the nearest time in relation to my system time. I have written the following method inside my model:

  def self.nearest_class(day, teacher_id, hour_min)
    day_of_week = '%' + day + '%'
    if hour_min == "06:00:00"
      where('frequency like ? AND teacher_id = ? AND (start_time >= ?)', day_of_week, teacher_id, hour_min).order('start_time ASC')
    else
      where('frequency like ? AND teacher_id = ? AND (start_time >= localtime(0))', day_of_week, teacher_id).order('start_time ASC')
    end
  end

When I tested the above method in rails console using Lecture.nearest_class('Monday', 1, 'localtime(0)'), the following query was returned together with some data:

SELECT "lectures".* FROM "lectures" WHERE (frequency like '%Monday%' AND teacher_id = 1 AND (start_time >= localtime(0))) ORDER BY start_time ASC

But I am expecting no record because my system time is greater than any start_time recorded in the database. I have used the query from the console to pgadmin3 to test if the results are same. However, pgadmin3 showed no results, as expected.

Are there differences in postgresql time and rails app time? How can I be able to check these differences? I tried Time.now in the console and it is the same as SELECT LOCALTIME(0) from pgadmin3. What should be the proper way to get the records for nearest system time?

To further set the context, here's my controller method:

def showlectures
    @teacher = Teacher.login(params[:id]).first
    @lecture_by_course = nil
    if @teacher
        WEEKDAY.each do |day|
            hour_min = "06:00:00"
            if day == Time.now.strftime("%A") # check if day of week is same e.g. 'Monday'
                hour_min = "localtime(0)" # system time with format: HH:MM:SS
            end
            @lecture_by_course = Lecture.nearest_class(day, @teacher.id, hour_min).first
            if @lecture_by_course
                break
            end
        end
    end

    render json: @lecture_by_course, include: { course: { only: [:name, :id] }}
end

Is your start_time field defined as with or without time zone? PostgreSQL localtime(precision) is without time zone, so you may have mismatched time zone expectations in the comparison. Try current_time instead. — dbenhur
– dbenhur, Commented Feb 11, 2013 at 3:18
@dbenhur: Rails will use a timestamp without timezone and force everything in the database to be in UTC. — mu is too short
– mu is too short, Commented Feb 11, 2013 at 4:15

janejanejane · Accepted Answer · 2013-02-25 14:32:30Z

1

I solved this by just passing the server Time.now instead of having a SQL check. { :| d'oh }

answered Feb 25, 2013 at 14:32

janejanejane

4962 gold badges12 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Difference between postgresql time and rails 3 app time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related