0

I have a requirement to fetch a UNIX timestamp (String), convert it into a date in a specific format, and store it in a MySQL DB in a DATETIME Column.

Here is my piece of code (simplified):

String ep ="a1527069600";
Long epoch = Long.parseLong(ep.substring(1, ep.length()));
Date dt = new Date(epoch*1000L);
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a");
a");
sdf.setTimeZone(TimeZone.getTimeZone("Asia/Calcutta"));
System.out.println("SDF.Format - "+sdf.format(dt));
System.out.println("SDF.parse - "+sdf.parse(sdf.format(dt)));

Console output:

SDF.Format - 23/05/2018 03:30:00 PM <br>
SDF.parse - Wed May 23 15:30:00 IST 2018

When I try to push this value in a MySQL DB, it throws Invalid DateError.

Just confused why sdf.format and sdf.parse shows timestamp in different formats.

Abhijeet Teli
  • 3
  • 2
  • What is the datatype of your MySQL column? Asking because you can probably push either a `LocalDateTime` or an `Instant` object and not worry about format at all. In any case I recommend you avoid the `SimpleDateFormat` class. It is not only long outdated, it is also notoriously troublesome. Today we have so much better in [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. May 23 '18 at 21:55
  • Possible duplicate of [return date type with format in java](https://stackoverflow.com/questions/50485203/return-date-type-with-format-in-java). – Ole V.V. May 23 '18 at 21:56
  • Mysql column type DATETIME, Thanks for suggestion on latest date time API, I think i should simply get rid of sdf classes. I will try to leverage new API's - Abhijeet – Abhijeet Teli May 24 '18 at 06:57
  • The `java.util` Date-Time API and their formatting API, `SimpleDateFormat` are outdated and error-prone. It is recommended to stop using them completely and switch to the [modern Date-Time API](https://www.oracle.com/technical-resources/articles/java/jf14-Date-Time.html). Check [this answer](https://stackoverflow.com/a/67752047/10819573) and [this answer](https://stackoverflow.com/a/67505173/10819573) to learn how to use `java.time` API with JDBC. – Arvind Kumar Avinash Jan 21 '23 at 16:59

1 Answers1

0

Don’t pass dates as strings to your MySQL database. It’s better, both easier and safer to pass date objects. For one thing, it relieves you of formatting and any formatting issues. Best, pass instances of a class from java.time, the modern Java date and time API. In your case a LocalDateTime will do.

    String ep ="a1527069600";
    long epoch = Long.parseLong(ep.substring(1));
    Instant inst = Instant.ofEpochSecond(epoch);
    LocalDateTime ldt = inst.atZone(ZoneId.of("Asia/Calcutta")).toLocalDateTime();

    System.out.println(ldt);

    PreparedStatement ps = myDatabaseConnection.prepareStatement(
            "insert into my_table (my_date_time) values (?)");
    ps.setObject(1, ldt);

The LocalDateTime obtained is:

2018-05-23T15:30

I simplified your conversion from epoch string a bit. Use a primitive, lowercase long rather than a Long object. I am using your time zone, Asia/Calcutta, assuming that your database values are in this time zone. Normal recommendations are to keep database values in UTC, at least if there’s any possibility that the data will ever be used outside your time zone. If you want this, use this conversion instead of the one above:

    LocalDateTime ldt = inst.atOffset(ZoneOffset.UTC).toLocalDateTime();

Link: Oracle tutorial: Date Time explaining how to use java.time.

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161