How to Convert Epoch to MySQL Timestamp in Java

How to convert epoch to mySQL timestamp in JAVA

Why not use a normal Date all along?

Calendar cal = Calendar.getInstance();
cal.add(Calendar.DAY_OF_MONTH, -7);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
String output = formatter.format(cal.getTime());

Timestamps and time zone conversions in Java and MySQL

Date-Time Handling Is A Mess

The first paragraph in the answer by Teo is quite insightful and correct: Date-time handling in Java is a mess. Ditto for all other languages & development environments that I know of. Date-time work is difficult and tricky, especially error-prone and frustrating because we think it of date-time intuitively. But "intuitively" does not cut it when it comes to data types, databases, serialization, localization, adjusting across time zones, and all the other formalities that come with computer programming.

Unfortunately, the computer industry basically chose to ignore this problem of date-time work. Just as Unicode took too long to be invented given the obvious need, so too has the industry kicked the can down the road on solving the problem of date-time handling.

Do Not Rely On Count-Since-Epoch

But I must disagree with its conclusion. Working with a count-since-epoch is not the best solution. Using count-since-epoch is inherently confusing and error-prone and incompatible.

Humans cannot read a long number and decipher that as a date-time. So verifying data and debugging becomes complicated, to say the least.
What "count" would you use? The milliseconds used by java.util.Date and by Joda-Time? The microseconds used by Postgres, MySQL, and other databases? The nanoseconds used by the new java.time package in Java 8?
Which epoch would you use? The Unix epoch of the beginning of 1970 in UTC is common, but far from singular. Almost two dozen epochs have been used by various computer systems.

We create numeric data types for doing math rather than using bits. We create string classes to handle the nitty-gritty details of handling text rather than bare octets. So too we should create data-types and classes to handle date-time values.

The early Java teams (and IBM & Taligent before them) made an attempt with the java.util.Date and java.util.Calendar and related classes. Unfortunately, the attempt was inadequate. While date-time is inherently confusing, these classes have added even more confusion.

Joda-Time

As far as I know, the Joda-Time project was the first project to take on date-time in a thorough, competent, and successful manner. Even so, the creators of Joda-Time were not entirely satisfied. They went on to create the java.time package in Java 8, and extend that work with the threeten-extra project. Joda-Time and java.time share similar concepts but are distinct, each having some advantages.

Database Problems

Specifically, the java.util.Date & .Calendar classes lack date-only values without time-of-day and time zone. And they lack time-only values without date and time zone. Before Java 8, the Java team added the hacks known as the java.sql.Date and java.sql.Time classes which is a date-time value masquerading as a date-only. Both Joda-Time and java.time rectify that by offering LocalDate and LocalTime classes.

Another specific problem is that java.util.Date has a resolution of milliseconds, but databases frequently use microseconds or nanoseconds. In an ill-advised attempt to bridge this disparity, the early Java team created another hack, the java.sql.Timestamp class. While technically a java.util.Date subclass, it also tracks the fractional seconds to nanosecond resolution. So when converting in and out of this type you may losing or gaining the finer fractional seconds granularity without being conscious of that fact. So that might mean that values you expect to be equal are not.

Another source of confusion is the SQL data type, TIMESTAMP WITH TIME ZONE. That name is a misnomer as the time zone info is not stored. Think of the name as TIMESTAMP WITH RESPECT FOR TIME ZONE as any passed time zone offset info is used in converting the date-time value to UTC.

The java.time package with its nanosecond resolution has some specific features to better communicate date-time data with a database.

I could write much more, but such information can be gleaned from searching StackOverflow for words such as joda, java.time, sql timestamp, and JDBC.

Example using Joda-Time with JDBC with Postgres. Joda-Time uses immutable objects for thread-safety. So rather than alter an instance ("mutate"), we create a fresh instance based on the values of the original.

String sql = "SELECT now();";
…
java.sql.Timestamp now = myResultSet.getTimestamp( 1 );
DateTime dateTimeUtc = new DateTime( now , DateTimeZone.UTC );
DateTime dateTimeMontréal = dateTimeUtc.withZone( DateTimeZone.forID( "America/Montreal" ) );

Focus On UTC

Before this, I thought timestamps were by convention always in UTC. Why on earth would anyone want a localized timestamp instead of a localized representation of it? Wouldn't that be very confusing for everyone?

Indeed. The SQL standard defines a TIMESTAMP WITHOUT TIME ZONE which ignores and strips away any included time zone data. I cannot imagine the usefulness of that. This Postgres expert, David E. Wheeler, says as much in recommending always using TIMESTAMP WITH TIME ZONE. Wheeler cites one narrow technical exception (partitioning) and even then says to convert all the values to UTC yourself before saving to the database.

The best practice is to work and store data in UTC while adjusting to localized time zones for presentation to the user. There may be times when you want to remember the original date-time data in its localized time zone; if so, save that value in addition to converting to UTC.

Guidelines

The first steps to better date-time handling are avoiding java.util.Date & .Calendar, using Joda-Time and/or java.time, focusing on UTC, and learning the behavior of your specific JDBC driver and your specific database (databases vary widely in their date-time handling despite the SQL standard).

MySQL

Caveat: I don’t use MySQL (I'm a Postgres kind of guy).

According to the version 8 documentation, the two types DATETIME and TIMESTAMP differ in that the first one lacks any concept of time zone or offset-from-UTC. The second one uses any indication of time zone or offset-from-UTC accompanying an input to adjust that value to UTC, then stores it, and discards the zone/offset info.

So these two types seem to be akin to the standard SQL types:

MySQL DATETIME ≈ SQL-standard TIMESTAMP WITHOUT TIME ZONE
MySQL TIMESTAMP ≈ SQL-standard TIMESTAMP WITH TIME ZONE

For MySQL DATETIME, use the Java class LocalDateTime. That class, like that data type, purposely lacks any concept of time zone or offset-from-UTC. Use this type and class for either:

When you mean any zone or all zones, such as “Christmas starts on first moment of December 25, 2018”. That translates to different moments in different places as a new day dawns earlier in the east than in the west.
When scheduling appointments or events far enough out in the future that politicians may change the offset of the time zone, for which politicians around the world have shown a proclivity. In this usage, you must at runtime apply a time zone to dynamically calculate, but not store, a moment for display on a calendar. That way, a 15:00 dental appointment in 8 months remains at 15:00 even if politicians redefine the clock to be minutes/hours ahead or behind.

For MySQL TIMESTAMP, use the Java class Instant, as shown above. Use this type and class for moments, specific point on the timeline.

JDBC 4.2

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database. Use getObject & setObject methods.

myPreparedStatement.setObject( … , Instant.now() ) ;

Retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

The JDBC 4.2 specification requires a driver to support OffsetDateTime but strangely does not require support for the more common types Instant and ZonedDateTime. But converting between types is quite easy.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
Instant instant = odt.toInstant() ;

You can then adjust that UTC value in Instant to a specific time zone for presentation to a user.

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, and later
Built-in.
Part of the standard Java API with a bundled implementation.
Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
Later versions of Android bundle implementations of the java.time classes.
For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

MySQL timestamp to Java date conversion

Your question is vague and ambiguous. I'll leave the timezone ambiguity away.

How can I convert time in unix timestamp to normal time?

I suspect that you're somehow obtaining a long or maybe a String value from the DB instead of a Date. In JDBC, you would normally like to use the appropriate methods to obtain the DB-specific datatypes. The MySQL TIMESTAMP datatype can be obtained by ResultSet#getTimestamp() which gives you a java.sql.Timestamp which in turn is a subclass of java.util.Date.

In a nut, the following should do:

Date date = resultSet.getTimestamp("columnname");

To format it further in a human readable format whenever you're going to present it to the enduser, use SimpleDateFormat. Click the link, it contains an overview of all patterns. Here's an example:

String dateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);

To do all the other way round, use respectively SimpleDateFormat#parse() and PreparedStatement#setTimestamp().

Converting milliseconds to Timestamp in Java

Use constructor.

new Timestamp(System.currentTimeMillis())

http://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html#Timestamp(long)

convert epoch time to date

EDIT: Okay, so you don't want your local time (which isn't Australia) to contribute to the result, but instead the Australian time zone. Your existing code should be absolutely fine then, although Sydney is currently UTC+11, not UTC+10.. Short but complete test app:

import java.util.*;
import java.text.*;

public class Test {
    public static void main(String[] args) throws InterruptedException {
        Date date = new Date(1318386508000L);
        DateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));
        String formatted = format.format(date);
        System.out.println(formatted);
        format.setTimeZone(TimeZone.getTimeZone("Australia/Sydney"));
        formatted = format.format(date);
        System.out.println(formatted);
    }
}

Output:

12/10/2011 02:28:28
12/10/2011 13:28:28

I would also suggest you start using Joda Time which is simply a much nicer date/time API...

EDIT: Note that if your system doesn't know about the Australia/Sydney time zone, it would show UTC. For example, if I change the code about to use TimeZone.getTimeZone("blah/blah") it will show the UTC value twice. I suggest you print TimeZone.getTimeZone("Australia/Sydney").getDisplayName() and see what it says... and check your code for typos too :)

How to Convert Epoch to MySQL Timestamp in Java