How to convert epoch to mySQL timestamp in JAVA

Question

How to get mySQL timestamp format in mySQLtimestamp?

long epochNow = System.currentTimeMillis()/1000;
long epochWeek = 604800;
long date7daysAgo = epochNo2013 w - epochWeek;
String mySQLtimestamp = /* 2013-09-23:50:00 */ 

Answer 1

java.time

With the release of Java SE 8 in March 2014, the outdated and error-prone legacy Date-Time API (java.util Date-Time types and their formatting type, SimpleDateFormat etc.) was supplanted by java.time, the modern Date-Time API^*. The following table depicts the mapping of ANSI SQL types with java.time types:

ANSI SQL	Java SE 8
DATE	LocalDate
TIME	LocalTime
TIMESTAMP	LocalDateTime
TIME WITH TIMEZONE	OffsetTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

Note that ZonedDateTime and Instant are not supported by any JDBC driver whereas some drivers e.g. PostgreSQL also do not support OffsetTime / TIME [ WITHOUT TIMEZONE ]. Also, note that all OffsetDateTime instances will have to be in UTC (have offset 0). This is because the backend stores them as UTC.

How to use it in JDBC?

Given below is a sample code to insert the current OffsetDateTime in UTC, into columnfoo (which is of TIMESTAMP WITH TIMEZONE type):

OffsetDateTime odt = Instant.now().atOffset(ZoneOffset.UTC);
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();

An Instant represents an instantaneous point on the timeline and is independent of a timezone i.e. it has a timezone offset of +00:00 hours.

Given below is a sample code to retrieve a OffsetDateTime from columnfoo:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE <some condition>");
while (rs.next()) {
    // Assuming the column index of columnfoo is 1
    OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class));
    System.out.println(odt);
}
rs.close();
st.close();

Just in case you need to convert an OffsetDateTime into another one with a different offset:

There are several ways to do so but I mostly use OffsetDateTime#withOffsetSameInstant, to convert an OffsetDateTime into another one with a different timezone offset e.g.

import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;

public class Main {
    public static void main(String[] args) {
        // A sample OffsetDateTime in UTC.
        OffsetDateTime odt = Instant.now().atOffset(ZoneOffset.UTC);
        System.out.println(odt);

        OffsetDateTime offsetTimeAtOffset0100 = odt.withOffsetSameInstant(ZoneOffset.of("+02:00"));
        System.out.println(offsetTimeAtOffset0100);

        // Time at JVM's default timezone offset
        ZoneOffset jvmTzOffset = ZonedDateTime.now(ZoneId.systemDefault()).getOffset();
        OffsetDateTime offsetTimeAtJvmTzOffset = odt.withOffsetSameInstant(jvmTzOffset);
        System.out.println(offsetTimeAtJvmTzOffset);
    }
}

Output:

2021-05-29T13:36:15.258076Z
2021-05-29T15:36:15.258076+02:00
2021-05-29T14:36:15.258076+01:00

Some points related to the code given above:

1. The Z in the output is the timezone designator for zero-timezone offset. It stands for Zulu and specifies the Etc/UTC timezone (which has the timezone offset of +00:00 hours).
2. The code converts odt into two instances of OffsetDateTime - each in a different way. The first instance is with a fixed timezone offset of +02:00 hours whereas the second one is with the timezone offset of the JVM. Note that the timezone offset of a place observing DST changes as per the summer/winter time. Therefore, if a place observes DST, instead of using a fixed timezone offset e.g. +02:00 hours; we should get it from the API.
3. My JVM's timezone is Europe/London and currently its offset is +01:00 hours.

Learn more about the modern date-time API^* from Trail: Date Time.

---

^{* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.}

Answer 2

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());