Page 1 of 1

Calculating difference between two timestamps

Posted: Mon Jul 27, 2015 9:17 pm
by manialluri
Hello,

I have a column in my database test data and which is defined as timestamp. For Ex the data in that column is saved as 2015-07-24 12:21:00

I need to extract all the data from that table where the Test data is between 2015-07-24 12:00:00 and 2015-07-24 13:00:00

I need to see all the data that has transactions between 1 hour. How to use that in the where clause.

When i use eqaul to >= or <= it is extracting all the data for that date but not at that particular time on that date.

Thanks in advance
Mani

Re: Calculating difference between two timestamps

Posted: Mon Jul 27, 2015 9:54 pm
by JimKnicely
Did you try the BETWEEN operator?

Code: Select all

dbadmin=> select * from test;
          c
---------------------
 2015-07-24 12:21:00
 2015-07-25 12:21:00
 2015-07-24 12:00:00
 2015-07-24 13:00:00
(4 rows)

dbadmin=> select * from test where c between '2015-07-24 12:00:00' and '2015-07-24 13:00:00';
          c
---------------------
 2015-07-24 12:00:00
 2015-07-24 12:21:00
 2015-07-24 13:00:00
(3 rows)

Re: Calculating difference between two timestamps

Posted: Mon Jul 27, 2015 10:09 pm
by manialluri
I used between operator but it is not working.

If I use >= also it is taking only date into consideration not the time. So if say test_date >='2015-07-24 12:00:00' i am getting all the data from that day instead of filtering from 12:00:00 on 2015-07-24.

Re: Calculating difference between two timestamps

Posted: Mon Jul 27, 2015 11:48 pm
by JimKnicely
It should, as you can see in my example. Can you describe your table (with the \d meta-command) and post the results? And can you post the exact SQL you are trying?

Re: Calculating difference between two timestamps

Posted: Tue Jul 28, 2015 9:05 am
by id10t
Hi!
manialluri wrote:If I use >= also it is taking only date into consideration not the time.
It should
manialluri wrote:So if say test_date >='2015-07-24 12:00:00' i am getting all the data from that day instead of filtering from 12:00:00 on 2015-07-24.
Probably implicit data type coercion. If LEFT SIDE(test_date) is from data type DATE so value '2015-07-24 12:00:00' will be converted to DATE. Try next:

Code: Select all

test_date::TIMESTAMP >= '2015-07-24 12:00:00'::TIMESTAMP