Format datetime with timezone

Question

I need to parse any incoming date time string with a user specified locale and timezone to the sole pattern to properly store it in the database later:

String inputDatetime = "Mon Dec 21 21:18:37 GMT 2020";
DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").withLocale(Locale.getDefault()).withZone(ZoneOffset.UTC);
TemporalAccessor date = fmt.parse(inputDatetime);

But I get the following error:

java.time.format.DateTimeParseException: Text 'Mon Dec 21 21:18:37 GMT 2020' could not be parsed at index 0

What's the problem with this code?

Answer 1

Assuming that your database has got a timestamp with time zone datatype, this is what you should use for storing the date and time from your string. Your input string unambiguously defines a point in time, and so does timestamp with time zone.

Next you should not store the date-time as a string in a particular format that your database likes. Store proper date-time objects. Since JDBC 4.2 this means objects of types from java.time, the modern Java date and time API that you are already using. Then you don’t need to care about format. It’s all taken care of for you. If your database datatype is timestamp with time zone, store an OffsetDateTime into that column. It instead it’s timestamp without time zone or datetime, instead store a LocalDateTime. The documentation of your JDBC driver should give you more details.

What's the problem with this code?

I see more than one problem with your code.

• As you said yourself in the comment, you are trying to parse a string using a formatter with pattern yyyy-MM-dd HH:mm:ss, but your string clearly is not in yyyy-MM-dd HH:mm:ss format. So this is bound to fail. More specifically the format string begins with yyyy for year of era, for example 2020. So the formatter expects to find a four digit number for year at the beginning of your string. Instead it finds Mon and throws the exception. The exception message informs us that the string could not be parsed at index 0. Index 0 is the beginning of the string, where the Mon is. I am not sure, but it seems that you have been confusing input and output format. Converting a date-time from a string in one format to a string in a different format involves two operations:
  1. First you parse your string into a date-time object using a formatter that describes the format of your original string.
  2. Second you format your datetime into a string using a formatter that describes the format of the resulting string.
• Since the original string is in English, you must use an English-speaking locale when parsing it. Using Locale.getDefault() will work on English-speaking devices and then suddenly fail when one day you run it on a device with a different language setting. So it’s a bad idea.
• TemporalAccessor is a low-level interface that we should seldom be using. Instead parse your string into a ZonedDateTime since it contains date, time and time zone (in a string GMT counts as a time zone).

If you were to format the date-time to the format of your DateTimeFormatter — which, as I said, I don’t think is what you should want — the following would work:

    DateTimeFormatter inputParser = DateTimeFormatter
            .ofPattern("EEE MMM dd HH:mm:ss zzz yyyy", Locale.ROOT);
    DateTimeFormatter databaseFormatter
            = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss");
    
    String inputDatetime = "Mon Dec 21 21:18:37 GMT 2020";
    OffsetDateTime dateTimeToStore = ZonedDateTime.parse(inputDatetime, inputParser)
            .toOffsetDateTime()
            .withOffsetSameInstant(ZoneOffset.UTC);
    String formattedString = dateTimeToStore.format(databaseFormatter);
    
    System.out.println(formattedString);

Output:

2020-12-21 21:18:37

Answer 2

As you have already guessed, the root cause of the error is a mismatch between the pattern the date-time string in and the one that you have used in the DateTimeFormatter. If you already know all the date-time patterns in which you are getting the date-time strings, you can create DateTimeFormatter with multiple optional patterns (by enclosing the patterns in square bracket). If you receive a date-time in an unknown pattern (i.e. the pattern which you have not put in DateTimeFormatter), you can throw the exception or handle it as per your requirement.

I need to parse any incoming date time string with a user-specified locale and timezone to the sole pattern to properly store it in the database later:

There are two parts of this requirement: A. Parse and convert the date-time in the user-specified locale and timezone into the equivalent date-time at UTC (not only recommended but also required by some databases e.g. PostgreSQL) B. Save it into the database.

The steps to meet the first part of the requirements are:

1. Since the received date-time is in the user-specified timezone, ignore the timezone contained in the date-time string and parse it to LocalDateTime.
2. Convert the LocalDateTime to ZonedDateTime at the user-specified timezone.
3. Convert this ZonedDateTime to ZonedDateTime at UTC.
4. Finally, convert the ZonedDateTime to OffsetDateTime.

Once you have OffsetDateTime, you can store it into the database as follows:

PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);// odt is the instance of OffsetDateTime
st.executeUpdate();
st.close();

You can use the following test harness to test the first part of the requirement:

import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Arrays;
import java.util.Locale;
import java.util.Objects;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) {
        // Test
        Scanner scanner = new Scanner(System.in);

        while (true) {
            System.out.print("Enter the date-time string (press Enter without entering anything to quit): ");
            String strDateTime = scanner.nextLine();
            if (strDateTime.isBlank()) {
                break;
            }

            boolean valid;

            // Create Locale
            Locale locale = null;
            do {
                valid = true;
                System.out.print("Enter language code e.g. en, fr, in: ");
                String languageTag = scanner.nextLine();
                if (!isValidForLocale(languageTag)) {
                    System.out.println("Invalid code. Please try again.");
                    valid = false;
                } else {
                    locale = Locale.forLanguageTag(languageTag);
                }
            } while (!valid);

            // Create ZoneId
            ZoneId zoneId = null;
            do {
                valid = true;
                System.out.print("Enter timezone in the format Continent/City e.g. Asia/Calcutta: ");
                String timezone = scanner.nextLine();
                try {
                    zoneId = ZoneId.of(timezone);
                } catch (Exception e) {
                    System.out.println("Invalid timezone. Please try again.");
                    valid = false;
                }
            } while (!valid);

            try {
                System.out.println(getDateTimeInUTC(strDateTime, locale, zoneId));
            } catch (DateTimeParseException e) {
                System.out.println("The date-time string has the following problem:\n" + e.getMessage());
                System.out.println("Please try again.");
            }
        }
    }

    static OffsetDateTime getDateTimeInUTC(String strDateTime, Locale locale, ZoneId zoneId)
            throws DateTimeParseException {
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("[uuuu-M-d H:m:s][EEE MMM d H:m:s zzz uuuu]", locale);

        // Ignore the timezone contained in strDateTime and parse strDateTime to
        // LocalDateTime. Then, convert the LocalDateTime to ZonedDateTime at zoneId.
        // Then, convert this ZonedDateTime to ZonedDateTime at UTC. Finally, convert
        // the ZonedDateTime to OffsetDateTime and return the same.
        ZonedDateTime zdt = LocalDateTime.parse(strDateTime, dtf).atZone(zoneId).withZoneSameInstant(ZoneOffset.UTC);
        return zdt.toOffsetDateTime();
    }

    static boolean isValidForLocale(String languageTag) {
        return Arrays.stream(Locale.getISOLanguages()).anyMatch(l -> Objects.equals(l, languageTag));
    }
}

A sample run:

Enter the date-time string (press Enter without entering anything to quit): Mon Dec 21 21:18:37 GMT 2020
Enter language code e.g. en, fr, in: en
Enter timezone in the format Continent/City e.g. Asia/Calcutta: Asia/Calcutta
2020-12-21T15:48:37Z
Enter the date-time string (press Enter without entering anything to quit): 2020-1-23 5:15:8
Enter language code e.g. en, fr, in: en
Enter timezone in the format Continent/City e.g. Asia/Calcutta: Asia/Calcutta
2020-01-22T23:45:08Z
Enter the date-time string (press Enter without entering anything to quit):