oracle sql:
select trunc( sysdate, 'Month') month
from dual
java:
java.sql.Date sqlDate = resultSet.getDate("month");
log.info(sqlDate);
DateTime dateTime = new DateTime(sqlDate.getTime());
log.info(dateTime);
dateTime = dateTime.withMillisOfDay(0);
log.info(dateTime);
output:
2012-01-01
2012-01-01T 01:00:00.000+07:00
2012-01-01T 00:00:00.000+07:00
where did the extra hour?
Use LocalDate.fromDateFields(date) to interpret the SQL date as local (ignoring time-zone). You can then use methods on LocalDate to get a DateTime if necessary (although if your object really is "just a date" then LocalDate is the right object to use.
java.sql.Timestamp and not java.sql.Date, so using new DateTime(java.sql.Timestamp.valueOf(someSqlDate)); did the trick.myResultSet // Using JDBC 4.2 or later.
.getObject ( … , LocalDate.class ) // Returns a `LocalDate` object.
.atStartOfDay ( ZoneId.of ( "America/Los_Angeles" ) ) // Let java.time determine the moment when the day begins on a particular date in a particular time zone.
.getMonth() // `java.time.Month` enum object.
.getDisplayName ( // Automatically localized.
TextStyle.FULL_STANDALONE ,
Locale.of ( "en" , "US" )
) // Returns a `String` object.
Update for a decade later since Question posted…
Both java.sql.Timestamp and java.sql.Date are part of the terribly flawed legacy date-time classes that have been supplanted by the modern java.time classes built into Java 8+.
JDBC 4.2+ requires every JDBC driver support of the java.time types.
| Legacy | Modern |
|---|---|
java.sql.Date |
java.time.LocalDate |
java.sql.Timestamp |
java.time.Instant (generally) java.time.OffsetDateTime (in JDBC) |
java.util.GregorianCalendar |
java.time.ZonedDateTime |
From a DATE type field in standard SQL representing a year-month-date, use java.time.LocalDate directly. Objects of this class represent a year, month, and day without the context of a time zone or offset from UTC.
LocalDate localDate = myResultSet.getObject ( … , LocalDate.class ) ;
If you want the first moment of the day on that date, specify a time zone. For any given moment the time of day varies around the globe by time zone.
ZoneId z = ZoneId.of ( "America/Los_Angeles" ) ;
ZonedDateTime zdt = localDate.atStartOfDay ( z ) ;
If you want the month of that moment, interrogate for a java.time.Month enum object.
Month month = zdt.getMonth() ;
SYSDATEHowever, the Oracle command SYSDATE returns a date and time of day without the context of a time zone or offset-from-UTC. In Java that maps to the class java.time.LocalDateTime.
LocalDateTime ldt = myResultSet.getObject ( … , LocalDateTime.class )
We can interrogate LocalDateTime for the month. But be aware that this implicitly relies upon the time zone used by the Oracle database server. Around the end/beginning of a month, the result here may be the month before or after the result you may expect.
sysdate. (In the table you might put the legacy GregorianCalendar as somewhat corresponding to the modern ZonedDateTime. It’s not relevant in the JDBC context, but may be for other readers in other situations.)Default constructor uses system default constructor, and that might be causing issues with regards to DST. I found the toDateMidnight function really useful when trying to make sure I was actually at midnight.
toDateMidngith returns a LocalDate object, and it can only represent dates, not times. Is that the problem? If so, then it may not be what you're looking for. If withMillisOfDay() works for you then stick with it.
withTimeAtStartOfDay to the DateTime class instead.You should try this code.
LocalDate issueDate = new LocalDate(new Date());
System.out.println(new java.sql.Date(issueDate.toDate().getTime()))
java.sql.Datethat was 1 hour ahead (presumably when time zones don’t match), could it then also be an hour behind? Any number of hours behind or ahead?