java.time.LocalDateTime to java.sql.Date

Question

I am trying to implement java.time to parse different date formats and return as java.sql.Date. However I am loosing time if I use java.sql.Date.valueOf(LocalDate date). How I can achive the same way as java.sql.Date(java.util.Date date.getTime())? Possible formats in my application are.

DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("HH:mm:ssZ"); DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); DateTimeFormatter datetimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssZ"); DateTimeFormatter datetimeFormatterMil = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSZ");

import java.sql.Date;
public Date oslcStringToDate(String str) throws ParseException
    {
        //remove colon in time zone
        Date date = null;
        if (!str.equalsIgnoreCase(NULL)) {
            int colonPos = str.length() - 3;
            str = str != null && str.length() > 3 && str.charAt(colonPos) == ':' ? 
                    str.substring(0, colonPos) + str.substring(colonPos + 1) 
                    : str.endsWith("Z") ? 
                        str.substring(0, str.length() - 1) + "+0000" 
                        : str;
            date = str.indexOf("T") > 0 ? 
                    str.indexOf(".") > 0 ? 
                        java.sql.Date.valueOf(LocalDateTime.parse(str, datetimeFormatterMil).toLocalDate())
                        : java.sql.Date.valueOf(LocalDateTime.parse(str, datetimeFormatter).toLocalDate())
                    : java.sql.Date.valueOf(LocalDateTime.parse(str, dateFormatter).toLocalDate());
        }
        return date;
    }

"loosing time"--> documentation of java.sql.Date: "To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero ..." (emphasis mine) || if java.sql.Date(java.util.Date date.getTime()) works, you can convert the LocalDateTime to an Instant and use the toEpochMilli() method to use instead of Date.getTime() [but java.sql.Date is not expected to carry time] — user16320675
– user16320675, Commented May 26, 2022 at 14:43
Give an example of your input string being passed to the oslcStringToDate method. — Basil Bourque
– Basil Bourque, Commented May 26, 2022 at 19:33
Please explain the real problem you're trying to solve. The equivalency is java.sql.Timestamp <>java.time.LocalDateTime and java.sql.Date <> java.time.LocalDate. java.sql.Date is not supposed to have a time component (it just appears to have it because of the awkward inheritance from java.util.Date). And given JDBC supports the java.time types, you should have no need to continue to use the legacy types likes java.sql.Date. — Mark Rotteveel
– Mark Rotteveel, Commented May 26, 2022 at 20:18

Basil Bourque · Accepted Answer · 2022-05-26 22:41:43Z

Do not mix legacy and modern classes

if I use java.sql.Date.valueOf(LocalDate date)

Never mix the terrible legacy classes with their replacement, the modern java.time classes defined in JSR 310.

When handed an object of a legacy class, immediately convert. You can convert to and fro by way of to…/from…/valueOf methods found on the old classes.

LocalDate ld = myJavaSqlDate.toLocalDate() ;

And the other direction.

java.sql.Date d = Date.valueOf( myLocalDate ) ;

Avoid legacy classes

With JDBC 4.2 and later, support for the java.time classes is required in your JDBC driver.

So use only the java.time classes. No need to ever use either Date, nor Calendar, SimpleDateFormat, Timestamp, etc.

Date-only values

The LocalDate class represents a date-only, without a time-of-day, without a time zone or offset-from-UTC. So a LocalDate is a year, month, and day — nothing more.

The java.sql.Date class pretends to represent a date-only value. But due to a tragically poor design decision, the class inherits from java.util.Date which does have a time-of-day. And even more confusing, the java.util.Date class is supposed to represent a moment as seen in UTC, but nevertheless contains a time zone used when generating text. These legacy classes are a master class in how to not do OOP.

You said:

However I am loosing time if I use java.sql.Date.valueOf(LocalDate date).

If you mean the resulting java.sql.Date has no time-of-day, yes, of course, that is a feature, not a bug. As mentioned above, objects of this class actually do have a time-of-day because of inheritance, but the class pretends to have a date-only (year-month-day).

You said:

How I can achive the same way as java.sql.Date(java.util.Date date.getTime())?

I cannot understand your goal here. So, after again advising against ever using these awful legacy classes, I will layout some of the possible types for use with SQL.

standard SQL	java.time ☑️	legacy ❌
`DATE`	`LocalDate`	`java.sql.Date`
`TIME`	`LocalTime`	`java.sql.Time`
`TIMESTAMP WITHOUT TIME ZONE`	`LocalDateTIME`	no support
`TIMESTAMP WITH TIME ZONE`	`OffsetDateTime`	`java.sql.Timestamp`

ISO 8601

Your Question is not clear, but apparently you are trying to parse text in the format of "yyyy-MM-dd'T'HH:mm:ss.SSSZ".

This format happens to comply with the ISO 8601 standard. The java.time classes use the standard formats by default when parsing/generating text. So no need to specify a formatting pattern.

Instant instant = Instant.parse( "2022-01-23T12:34:56.789Z" ) ;

To generate such text:

String output = instant.toString() ;

To record to a database, convert to the class mapped in JDBC to the standard SQL type of TIMESTAMP WITH TIME ZONE.

That SQL name is a misnomer, as the SQL standard was written with mere offset-from-UTC in mind, not real time zones. An offset is merely a number of hours-minutes-seconds ahead or behind the temporal prime meridian of UTC. A time zone, in contrast, is a named history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Note that LocalDateTime is exactly the wrong class to be using in this context. The LocalDateTime class purposely lacks the context of an offset or zone. With only a date and a time-of-day, a LocalDateTime object is inherently ambiguous with regard to the timeline.

If you want to store only the date portion of your ISO 8601 string, you will need to think about time zones. For any given moment, the date varies around the globe by time zone. A moment can be “tomorrow” in Tokyo Japan while simultaneously “yesterday” in Toledo Ohio US.

Call toLocalDate to extract the date portion of an OffsetDateTime or ZonedDateTime object.

LocalDate dateInTokyo = instant.atZone( ZoneId.of( "Asia/Tokyo" ) ).toLocalDate() ;
LocalDate dateInUtc = instant.atOffset ZoneOffset.UTC ).toLocalDate() ;    
LocalDate dateInToledo = instant.atZone( ZoneId.of( "America/New_York" ) ).toLocalDate() ;  // Time zone name for Toledo Ohio US is `America/New_York`.

Write to a database column of a type akin to the SQL standard type DATE.

myPreparedStatement.setObject( … , dateInTokyo ) ;

Retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

All of these issues have been covered many many times already in Stack Overflow. Search to learn more.

The legacy equivalent for TIME/java.time.LocalTime is java.sql.Time

Collectives™ on Stack Overflow

java.time.LocalDateTime to java.sql.Date

1 Answer 1

Do not mix legacy and modern classes

Avoid legacy classes

Date-only values

ISO 8601

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Do not mix legacy and modern classes

Avoid legacy classes

Date-only values

ISO 8601

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related