CODEWITHSUNDEEP
sqlpostgresql
BASKA
BASKA

Reputation: 400

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

Upvotes: 0

Views: 60

Answers (1)

Antonio Alvarez
Antonio Alvarez

Reputation: 476

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'

Upvotes: 1

Related Questions