tl;dr
Using Half-Open query logic:
SELECT *
FROM tbl
WHERE when >= ? AND when < ? -- Half-Open logic, where beginning is inclusive while the ending is exclusive.
;
Query with two parameters:
- start-of-day moment
- start-of-following-day moment
Using java.time classes in modern Java:
myPreparedStatement.setObject(
1 ,
LocalDate.parse( "2013-03-15" ) // Represent entire day as a `LocalDate` object.
.atStartOfDay( // Determine first moment of that date in a particular time zone, which may or may not be 00:00:00.
ZoneId.of( "Asia/Kolkata" )
) // Returns a `ZonedDateTime` object. Your JDBC driver should be able to handle that smartly. If not, extract a `Instant` object in UTC by calling `ZonedDateTime::toInstant()`.
) ;
myPreparedStatement.setObject(
2 ,
LocalDate.parse( "2013-03-15" )
.plusDays( 1 ) // Move to the following day.
.atStartOfDay(
ZoneId.of( "Asia/Kolkata" )
)
) ;
Upon retrieval, adjust into any desired time zone.
myResultSet.getObject( … , Instant.class) // Extract a `Instant` object from selected row.
.atZone( // Apply a `ZoneId` to get a `ZonedDateTime` object.
ZonedId.of( "America/Montreal" )
)
Server default time zone irrelevant
Your server’s current default time zone should be irrelevant to your programming. You should be specifying the desired/expected time zone via optional arguments to the various date-time methods.
Ditto for Locale, by the way: Specify explicitly rather than rely on current default implicitly.
In my opinion, those arguments should be required rather than optional. Seems that a great many programmers ignore the issues of time zone and locale.
java.time
The modern approach uses java.time time classes.
With a JDBC driver compliant with JDBC 4.2 or later, you can directly exchange java.time types with your database.
For a moment in UTC, use Instant. For a date-only without a time-of-day and without a time zone, use LocalDate.
LocalDate ld = LocalDate.parse( "2013-03-15" ) ;
To search the database records holding a moment within the range of an entire day, we need to determine the first moment of that day and the first moment of the following day. Then we do a Half-Open query where the beginning is inclusive while the ending is exclusive.
Do not assume the day starts at 00:00:00. Anomalies such as Daylight Saving Time (DST) means the day may start at some other time such as 01:00:00. We need a time zone for this.
Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).
ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
ZonedDateTime zdtStart = ld.atStartOfDay( z ) ; // Determine when that particular day begins in that particular zone.
And the following day. Call LocalDate::plusDays to move to the following day.
ZonedDateTime zdtStop = ld.plusDays( 1 ).atStartOfDays( z ) ;
If you want to see that same moment as UTC wall-clock time, extract an Instant. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
Instant start = zdtStart.toInstant();
Instant stop = zdtStop.toInstant() ;
Your SQL should look something like:
String sql = "SELECT * FROM tbl WHERE when >= ? AND when < ? ;" ; // Half-open logic, where beginning is inclusive while the ending is exclusive.
Pass values for the placeholders.
myPreparedStatement.setObject( 1 , start ) ; // You can exchange java.time object with JDBC 4.2 or later.
myPreparedStatement.setObject( 2 , stop ) ;
And retrieval.
Instant instant = myResultSet.getObject( "when" , Instant.class ) ;
If you want to view that same moment through the lens of a wall-clock time used by the people of another region (time zone), apply a ZoneId to get a ZonedDateTime.
ZonedDateTime zdt = instant.atZone( ZoneId.of( "America/New_York" ) ) ;
UTC
Just a tip: Generally best to think, store, exchange, and log values in UTC. Adjust into a time zone only when the business logic or user interface demand it.
When at work programming or administrating, forget about your own parochial time zone and think in UTC instead. Keep a clock in UTC on your desk. Think of UTC as the One True Time™ and all other zones are but mere variations.
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.
With a JDBC driver complying with JDBC 4.2 or later, you may exchange java.time objects directly with your database. No need for strings or 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.
