CODEWITHSUNDEEP
javapostgresqlstored-procedures
Michael 'Maik' Ardan
Michael 'Maik' Ardan

Reputation: 4393

PostgreSQL Between clause

I have this sample statement here:

WHERE e.modified_date::DATE between '2013-04-02'::DATE
AND '2013-04-02'::DATE

Note:
Those dates were actually variables.

In pgAdmin, running this will result to this sample table:

username  modified_date
Sample     2013-04-02
Sample1    2013-04-02

This is my desired result.

However, calling the proc from Java would result to nothing. Changing the second date will return the previous sample table. It seems that PostgreSQL is evaluating the date if it is less than the second variable. Am I missing something?

Note: modified_date is timestamp

Upvotes: 1

Views: 438

Answers (1)

Michael 'Maik' Ardan
Michael 'Maik' Ardan

Reputation: 4393

Column details:

modified_date : TIMESTAMP

Here's an actual values inserted in modified_date field

modified_date
timestamp without time zone

2013-04-02 14:05:45.8611
2013-04-02 14:09:14.5422

A brief explanation why the test procedure with the WHERE clause of

WHERE e.modified_date >= '2013-04-02'::TIMESTAMP 
AND e.modified_date <='2013-04-02'::TIMESTAMP

returns nothing:

- Casting the String value '2013-04-02' to a TIMESTAMP datatype will result to '2013-04-02 00:00:00'
- First, the WHERE clause validates if the field modified_date (Refer to values) is greater than or equal to '2013-04-02 00:00:00'; which on the other hand, will return as false
- Second, the WHERE clause validates of the field modified_date (Refer to values) less than or equal to '2013-04-02 00:00:00'; which will return as false

This explanation only shows that the SELECT statement will only return a value that corresponds to the WHERE clause if and only if the modified_date is exactly '2013-04-02 00:00:00'

As for this WHERE clause:

WHERE e.modified_date::DATE BETWEEN '2013-04-02'::DATE
AND '2013-04-02'::DATE


Casting both modified_date and the String value to a DATE datatype will result to the following values:
modified_date as TIMESTAMP = '2013-04-02 14:05:45.8611'
modified_date as DATE = '2013-04-02'

modified_date as TIMESTAMP = '2013-04-02 14:09:14.5422'
modified_date as DATE = '2013-04-02'

String value as STRING = '2013-04-02'
String value as DATE = '2013-04-02'

NOTE:
This can also be done without casting the String value to DATE datatype. Casting the String on my part was for my validation purposes.

To answer my question, with the WHERE clause of:

WHERE e.modified_date::DATE between '2013-04-02'::DATE
AND '2013-04-02'::DATE

doesn't return anything is just I was calling the test stored procedure that uses the wrong WHERE clause.

Upvotes: 2

Related Questions