Skip to content
Advertisement

date time comparison with > does not work

I have two SQL statements:

1) (now i use this)

SELECT count(id) 
FROM public.user_event
where action_type='1' 
  and ip='17.24.25.18'  
  and date_occured between '2019-11-26  13:20:00' and now()  

2) (but i need to use this)

SELECT count(id) 
FROM public.user_event
where action_type='1' 
  and date_occured > '2019-11-26  13:20:00' 
  and ip='17.24.25.18'  

first query return 11 lines query 2 return 388.

my observation (about query 2) is that instead of “2019-11-26 13:20:00” it is taken “2019-11-26” and when I replace it really returns the same answer

date_occured is of type of timestamp without time zone

Advertisement

Answer

As your date_occured is a timestamp without time zone field, you would have to indicate it (otherwise your string would be automatically casted as a date):

SELECT count(id)
FROM public.user_event
WHERE action_type='1'
and date_occured > to_timestamp('2019-11-26 13:20:00', 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone
    AND ip='17.24.25.18'

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement