-3

I am trying to parse the datetimeoffset. This is from a SQL Server database table's column that was created as datetimeoffset lengh=10, precision=34, scale=7

Example from one of the records: 2024-07-16 22:30:00.0000000 -04:00

In this case, I would like to get back -4 Hours, and also convert this to user's local time.

Unfortunately, I cannot use T-SQL to do this. like (datename(tz, value)

I have the following :

import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) throws Exception {
        DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss:SSS z");

        //Date string with zone information
        //String dateString = "08/03/2019T16:20:17:717 UTC+05:30";
        String dateString = "2024-07-16 22:30:00.0000000 -04:00";

        //Instance with given zone
        ZonedDateTime zdtInstanceAtOffset = ZonedDateTime.parse(dateString, DATE_TIME_FORMATTER);

        //Instance in UTC
        ZonedDateTime zdtInstanceAtUTC = zdtInstanceAtOffset.withZoneSameInstant(ZoneOffset.UTC);

        //Formatting to string
        String dateStringInUTC = zdtInstanceAtUTC.format(DATE_TIME_FORMATTER);

        System.out.println("Hello world!");

        System.out.println(zdtInstanceAtOffset);
        System.out.println(zdtInstanceAtUTC);
        System.out.println(dateStringInUTC);

        //Convert ZonedDateTime to instant which is in UTC
        System.out.println(zdtInstanceAtOffset.toInstant());
    }
}

I get this error:

Exception in thread "main" java.time.format.DateTimeParseException: Text '2024-09-17 00:00:00.0000000 -04:00' could not be parsed at index 2

Any suggestions?

Improve this question
0

2 Answers 2

Reset to default
4

Your question supposes a plan. The plan it supposes is this:

  • Lets ask the database for this offset time value, but, ask it to convert it to a string first.
  • Then lets grab that string from java and do some fancy parsing to convert that right back offset info.
  • That second step phases me, I shall ask an SO question.

You've gone about it incorrectly. The above plan is a bad plan. Hence, your question is irrelevant. It's asking a question about a part of a bad plan.

I'm going to suppose something as well: That somewhere along the line you have resultSet.getTimestamp or similar (where resultSet is a JDBC ResultSet instance), and you found out (correctly) that this value is problematic.

The right plan is to instead do this:

OffsetDateTime odt = resultSet.getObject(colIdxOrName, OffsetDateTime.class);

And take all you need from this odt. You can just ask it for the offset directly. This way no string conversion occurs at all, you are not dependent on the tzdata defs of either your db engine or your JDK instance, and the API experience is vastly improved.

ZoneOffset offset = odt.getOffset() ;

JDBC 4.2+ requires that JDBC drivers support .getObject(colIdxOrName, OffsetDateTime.class) (along with ODT, also Instant and LocalDate). These are correct, and getDate and getTimestamp are wrong. You should never use getDate and getTimestamp, because the SQL types they return extend java.util.Date, and j.u.Date is broken. In the sense that j.u.Date does exactly what its spec says, but, its spec is sheer and utter lunacy. For starters, the name is a complete lie; j.u.Date instances explicitly do not represent dates. They represent instants; they are the old API equivalent of java.time.Instant. That's why all the date-relevant methods (such as .getYear()) are deprecated.

The fix is to never use j.u.Date which you do by using the relevant types in java.time instead (there is a reason java ships with a new date/time API: Because the old one is very bad, borderline broken, and certainly highly surprising).

For... reasons, JDBC does not have a .getLocalDateTime() method and never will. .getObject(X, LocalDateTime.class) is it. Officially. In both senses: DBs have to support it, and JDBC considers that to be the canonical way to grab an LDT out of a database (same applies to ODT and Instant and LD). Presumably because otherwise the amount of .getX() method would never end.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

100% agree with your comment. For anyone coming at this same problem but from the "but how do I do it in the database?", the approach is the same, if not the particulars. Specifically, use datepart(tzoffset, @yourValue) to get the offset directly rather than trying to parse it out of a string representation.
This is coming from an API. I must use the API. Irrelevant/unhelpful comments not needed!
-1

g00se posted this answer as a comment:

 DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.n z");
        OffsetDateTime odt = OffsetDateTime.parse("2024-09-17 00:00:00.0000000 -04:00", dtf);

        System.out.println(odt);

        ZoneOffset o = odt.getOffset();
        System.out.printf("Offset is %s%n", o);

        OffsetDateTime odtLocal = odt.withOffsetSameInstant(ZonedDateTime.now().getOffset());

        System.out.printf("As local OffsetDateTime is %s%n", odtLocal);

It works perfectly well. But I should probably not be encouraging this as I don't believe you're forced to work with strings

Improve this answer

1 Comment

I'm glad you retained my comment, but posting it as an answer simply encourages an incorrect approach. If you want to use my comments as answers, at least get the formatting right ;)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.