CODEWITHSUNDEEP
javapostgresql
learner
learner

Reputation: 1

How to get all data between 2 timestamp in postgres.

I have 2 timestamp fileds in postgres and I am representing this 2 fields in java as Date fields. start time --> oct 14 10 pm end time --> oct 14 11 pm if I pass a value as "oct 14 10.30 pm" then i must all rows whose start time greater than my passed value and end time less than my passed value

Upvotes: 0

Views: 342

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338316

tl;dr

Use Half-Open approach, >= AND < logic, for a span-of-time, never BETWEEN.

Incorrect logic

start time --> oct 14 10 pm end time --> oct 14 11 pm … then i must all rows whose start time greater than my passed value and end time less than my passed value

This logic is incorrect. Doing such hourly reports with such logic, You will never report on the items that happen to be logged when the clock strikes an hour. Rows with a value right at 10 AM exactly (10:00:00.0) or 11 AM exactly (11:00:00.0) will never be found.

Half-Open

I suggest making consistent use of the Half-Open approach to define span-of-time. In this approach, the beginning is inclusive while the ending is exclusive. For example, the hour begins with the first moment of the 10 AM hour, and runs up to, but does not include, when the clock strikes 11 AM.

With this approach a series of hourly queries will I feed find all rows. No rows will be disappearing as they would with your logic. 

SELECT * 
FROM event_
WHERE when_ >= ?
AND when_ < ?
;

In other words, never use BETWEEN with a span-of-time. Use >= AND < logic instead.

Time zone

The question of time zone is crucial if talking about actual moments, if talking about the SQL-standard column type of TIMESTAMP WITH TIME ZONE.

In your example, do you mean 10 AM in Paris France? Or 10 AM in Montréal Québec, some hours later? Or 10 AM in Kolkata India, some hours earlier?

In Java, define your criteria as moments using a time zone with the ZonedDateTime class. 

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
LocalDate ld = LocalDate.of( 2018 , 10 , 14 ) ;
LocalTime lt = LocalTime.of( 10 , 0 ) ;
ZonedDateTime zdt = ZonedDateTime.of( ld , lt , z ) ;

To view that same moment in UTC, extract an Instant. 

Instant instant = zdt.toInstant() ;

Generate text representing its value in standard ISO 8601 format. 

String output = instant.toString() ;   // Generate text in standard ISO 8601 format.

Pass that to the SQL query in a prepared statement using a JDBC driver compliant with JDBC 4.2 or later. 

myPreparedStatement.setObject( 1 , zdtStart ) ;
myPreparedStatement.setObject( 2 , zdtStop ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Upvotes: 3

Related Questions