Unparseable date: "Thursday 01 March 2018 12:43:38 PM IST"

Question

I am getting a parse exception when inserting "Thursday 01 March 2018 12:43:38 PM IST" into MySQL as a timestamp.

How can I convert this string for insertion into a MySQL timestamp column?

You likely didn't specify `Locale.US`, to parse day and month names in the correct language. — Andreas, Mar 01 '18 at 17:34
@ifly6 It is, but since OP is parsing a time zone [`IST`](https://www.timeanddate.com/time/zones/ist) (India Standard Time), OP may be using a PC with a `hi_IN` (Hindi) locale. — Andreas, Mar 01 '18 at 17:41
Welcome to Stack Overflow. Could you [create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve), please? Without it we don’t stand much of chance of guessing what went wrong, so we cannot really help you. — Ole V.V., Mar 01 '18 at 17:56
The `java.sql.Timestamp` class is long outdated. These days rather convert your date-time to a `java.time.Instant` and insert it into your database. — Ole V.V., Mar 01 '18 at 17:57
IST may be understood as Irish Summer Time, Israel Standard Time or India Standard Time. And may be interpreted differently on different computers. Those three and four letter time zone abbreviations are dangerous. Could you get something unambiguous, for example a string with a UTC offset in it, instead? — Ole V.V., Mar 02 '18 at 06:28

alasnd · Answer 1 · 2018-03-02T13:38:59.087

Without seeing your code, it's hard to tell exactly why you're getting an error, but anyway...

A parse exception usually occurs because the formatter/parser class has found a problem; either the pattern you use doesn't match the string you're trying to parse, or some config is wrong, or anything like that. So let's see it:

the month and day of week are in English, so you must set a java.util.Locale, otherwise it'll use the system's default
IST, as others already told, is ambiguous: it can refer to India, Israel, Ireland and who knows where else. So you must choose a proper timezone as well - I'm assuming you're in India, but you can change the code accordingly

In Java, you could do:

// use English locale
SimpleDateFormat sdf = new SimpleDateFormat("EEEE dd MMMM yyyy hh:mm:ss a zzz", Locale.ENGLISH);
// use India timezone
sdf.setTimeZone(TimeZone.getTimeZone("Asia/Kolkata"));

String s = "Thursday 01 March 2018 12:43:38 PM IST";
Date date = sdf.parse(s);
Timestamp timestamp = new Timestamp(date.getTime());

But SimpleDateFormat is full of problems: https://eyalsch.wordpress.com/2009/05/29/sdf/

For Java 8 or higher, there's a better API for that:

// use India timezone
Set<ZoneId> preferredZones = new HashSet<>();
preferredZones.add(ZoneId.of("Asia/Kolkata"));

// use English locale
DateTimeFormatter formatter = new DateTimeFormatterBuilder()
    .appendPattern("EEEE dd MMMM yyyy hh:mm:ss a ")
    .appendZoneText(TextStyle.SHORT, preferredZones)
    .toFormatter(Locale.ENGLISH);

String s = "Thursday 01 March 2018 12:43:38 PM IST";
ZonedDateTime zdt = ZonedDateTime.parse(s, formatter);
System.out.println(zdt);

Timestamp timestamp = Timestamp.from(zdt.toInstant());

Note that I used "Asia/Kolkata" as timezone. Those names in the format "Continent/City" are the standard used by Java, while short names such as "IST" are ambiguous and should be avoided. Fortunately, the API provides a way to workaround this ambiguity, but you must choose the proper zone to work with.

About formats

As a last note, keep in mind that date objects don't have a format: https://codeblog.jonskeet.uk/2017/04/23/all-about-java-util-date/

When you save a Date, Timestamp or any other date object to a database, you're saving their values, and those dates are not in any specific format. Strings like "Thursday 01 March 2018 12:43:38 PM IST" are just a representation of the date values, but it doesn't mean they are saved in that format.

Unparseable date: "Thursday 01 March 2018 12:43:38 PM IST"

1 Answers1

About formats