Postgresql seem to convert timestamp parameter of PreparedStatement

Question

Postgresql seem to convert timestamp parameter of PreparedStatement which I set using setTimestamp.

[What I want to do]

I want to query today's data. ( 2016-06-30 00:00:00 ~ 2016-06-30 23:59:59)

But, when I got the result from DB, it was data for 2016-06-29 15:00:00 to 2016-06-30 14:59:59. ( 9 hours gap)

My local timezone : GMT+9 (KST)

DB timezone : UTC (GMT+0) ( In table, UTC time is stored as update time. I checked that. )

So 9 hours gap as I guess. When I pass UTC timestamp parameter to postgresql, it subtracted 9 hours from my timestamp parameters. I wonder why postgresql did so, and how I prevent that.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
sdf2.setTimeZone(TimeZone.getTimeZone("UTC"));
Timestamp startTime = Timestamp.valueOf(sdf.format(new Date(System.currentTimeMillis())));
Timestamp endTime = Timestamp.valueOf(sdf2.format(new Date(System.currentTimeMillis())));

PreparedStatement pstmt = conn.prepareStatement( 
 "SELECT * FROM some_table WHERE update_time BETWEEN ? AND ? "
);
pstmt.setTimestamp(1, startTime);
pstmt.setTimestamp(2, endTime);
ResultSet rs = pstmt.executeQuery();

[Table structure]

CREATE TABLE some_table
(
mem_no      bigserial
,data   char(2)
,update_time    timestamp with time zone DEFAULT current_timestamp
,CONSTRAINT pk_some_table PRIMARY KEY (mem_no)
);

[Something strange]

Using debugging tool, I checked pstmt value. Strangely +09:00:00 was added to my parameters.

pstmt => SELECT * FROM some_table WHERE update_time BETWEEN 2016-06-30 00:00:00 +09:00:00 AND 2016-06-30 23:59:59 +09:00:00

DB : postgresql 9.3

Actually not.. ^^ But I guess while executing query, implicit conversion occurs in 'WHERE' clause. If not, I have no ideas .. — arayo
some_table is updated when user check today's data. At that time update time will be written. I use following queries. When update => UPDATE some_table SET data = ? , update_time = current_timestamp WHERE mem_no = ? When insert => INSERT INTO some_table (mem_no, data) VALUES ( ?, ? ) — arayo
Ok. thanks. What I meant - can you do a describe some_table in your postgresql and give the output — aksappy

Basil Bourque Basil Bourque · Accepted Answer · 2019-12-26T23:53:06

tl;dr

To find all rows with a recorded moment occurring on a certain date, in this SQL:
"SELECT * FROM tbl WHERE when !< ? AND when < ? ; " ; that uses the Half-Open approach to a span-of-time.

myPreparedStatement
.setObject( 
    1 , 
    LocalDate                   // Represent a date-only value, without a time-of-day and without a time zone.
    .parse( "2016-06-30" )      // Returns a `LocalDate` object.
    .atStartOfDay()             // Returns a `OffsetDateTime` object. JDBC 4.2 and later requires support for this class.
) ;

myPreparedStatement
.setObject( 
    2 , 
    LocalDate
    .parse( "2016-06-30" )
    .plusDays( 1 )              // Add a day, to get first moment of the following day.
    .atStartOfDay()
) ;

Details

There are multiple problems with your code. For more discussion, see other Answers such as this one of mine. I'll be brief here as this has been covered many times already.

Use only java.time classes, never the legacy date-time classes they supplanted as of adoption of JSR 310.
Use Half-Open approach to defining a span of time, where the beginning is inclusive while the ending is exclusive.
Be clear on whether you are tracking moments, specific points on the timeline, or vague date with time-of-day but lacking the context of a time zone or offset-from-UTC.
If tracking moments, your column must be of data type TIMESTAMP WITH TIME ZONE rather than WITHOUT.
For any given moment, the date (and time-of-day) varies around the globe by time zone.
Some dates in some time zones do not start at 00:00. Always let java.time determine start of day.

Apparently you want all the rows with a date-time occurring on the date of 2016-06-30.

LocalDate startDate = LocalDate.parse( "2016-06-30" ) ;
LocalDate stopDate = startDate.plusDays( 1 ) ;

Specify the time zone by which you want to interpret the date and get first moment of the day.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;

Get the first moment of start and stop dates.

ZonedDateTime zdtStart = startDate.atStartOfDay( z ) ;
ZonedDateTime zdtStop = startDate.atStopOfDay( z ) ;

Support for the ZonedDateTime class is not required in JDBC 4.2. That class may or may not work with your JDBC driver. If not, use the OffsetDateTime class, support for which is required by JDBC 4.2.

OffsetDateTime start = zdtStart.toOffsetDateTime() ;
OffsetDateTime stop = zdtStop.toOffsetDateTime() ;

Write your SQL with placeholders ?. I suggest always including the statement terminator ;. Never use BETWEEN for date-time work as that is full-closed rather than half-open.

String sql = "SELECT * FROM tbl WHERE when !< ? AND when < ? ; " ;  // Half-Open span-of-time where beginning is inclusive while the ending is exclusive.

Pass the placeholder objects.

myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , stop ) ;

Retrieve result.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

That result will be in UTC. You can adjust to your desired time zone. Same moment, same point on the timeline, but different wall-clock time.

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Always specify zone/offset

My local timezone : GMT+9 (KST)

DB timezone : UTC (GMT+0) ( In table, UTC time is stored as update time. I checked that. )

Write your Java app such that you never depend on the current default time zone of either your JVM or your database server.

Notice that the code above using objects has no surprises with time zone, and explicitly specify desired/expected time zones or offsets. The surprises come with middleware and utilities that are opinionated about injecting a zone or offset into the retrieved values. Postgres itself always stores and retrieves TIMESTAMP WITH TIME ZONE values in UTC (an offset of zero hours-minutes-seconds).

Half-Open

I want to query today's data. ( 2016-06-30 00:00:00 ~ 2016-06-30 23:59:59)

No, you are missing the last second of the day there.

Instead define your spans-of-time using Half-Open approach where the beginning is inclusive while the ending is exclusive. This lets spans of time neatly abut one another without gaps and without overlaps.

So a week starts on Monday and runs up to, but does not include, the following Monday. Lunch period starts at 12:00 noon and runs up to, but does not include, when the clock strikes 13:00. A day starts at the first moment of the day (which is not always 00:00 by the way!), running up to, but not including, the first moment of the following day. Study the SQL and Java shown in this Answer to see how that works.