Reputation: 1

java.time.LocalDateTime to java.sql.Date

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;
    }

Upvotes: 0

Answers (1)

Basil Bourque

Reputation: 340025

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 ) ;