3

I have date and time stored in postgres as timestamp format in postgres like this:

created_time timestamp without time zone DEFAULT now(),

now I want to search for rows that have created_time between date_before and date_after fields. both these fields are query strings.

How can i filter data with this?

Improve this question
4
  • In what format the strings are? Commented Jun 16, 2021 at 7:43
  • just date and time without timestamp Commented Jun 16, 2021 at 7:47
  • 2020-06-22 19:10:25-07 Commented Jun 16, 2021 at 7:48
  • wiki.postgresql.org/wiki/… Commented Jun 16, 2021 at 8:04

1 Answer 1

Reset to default
3

In Postgres, you can simply compare the timestamp with the strings:

select *
from your_table
where created_time >= '2020-06-22 19:10:25-07'
and created_time < '2020-06-23 19:10:25-07'

Remember not to use between with time stamps

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

11 Comments

@Kayaman No it doesn't. The problem with between happens when timestamps are compared with dates without time. OP stated he was to compare date with times
@rudeTool I just realised that your column is a timestamp without time zone. You should use timestamp with time zone as you are trying to compare timestamps with time zones.
my query params doesnt have timezone
Please see wiki.postgresql.org/wiki/… Using TS without TZ is a bad practice.
Also the example TS you put in your question comment has a time zone
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.