CODEWITHSUNDEEP
javamysqldatetimejava-8
Joe
Joe

Reputation: 279

Inserting into database a datetime with the current UTC time

I'm attempting to insert a datetime into a mysql database for the current UTC time via the GETUTCDATE() function in sql. It's failing with "FUNCTION GETUTCDATE DOES NOT EXIST".

Is a way for me to get the current UTC time in sql datetime format from Java, and simply insert it as a string?

Another big issue I'm having is I need to convert the above utc datetime object into local time zones and I don't really know how to do that through standard java api's.

Upvotes: 0

Views: 2545

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338835

tl;dr

myPreparedStatement      // Using a `PreparedStatement` avoids SQL-injection security risk.
.setObject(              // As of JDBC 4.2, we can exchange java.time objects with a database via `getObject`/`setObject` methods.
    … ,                  // Indicate which `?` placeholder in your SQL statement.
    OffsetDateTime.now( ZoneOffset.UTC )  // Capture the current moment in UTC.
) ;

java.time

The modern solution uses the java.time classes that years ago supplanted the terrible old date-time classes.

Get the current moment in UTC using OffsetDateTime.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

MySQL 8.0 uses a resolution of microseconds, for six decimal places in a fractional second. The java.time classes carry a finer resolution of nanoseconds. So you may want to truncate any existing nanos from your OffsetDateTime. Specify your desired resolution with ChronoUnit.

OffsetDateTime odt = 
    OffsetDateTime
    .now( 
        ZoneOffset.UTC 
    ) 
    .truncatedTo( ChronoUnit.MICROS ) 
;

Send to your database via a PreparedStatement to a column of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE data type. For MySQL 8.0, that would be the type TIMESTAMP. 

myPreparedStatement.setObject( … , odt ) ;

And retrieval via a ResultSet.

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

To see this moment through the lens of the wall-clock time used by the people of a particular region (a time zone), apply a ZoneId to get a ZonedDateTime object. 

ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;  // Same moment, same point on the timeline, different wall-clock time.

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?

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.

Upvotes: 2

Taher A. Ghaleb
Taher A. Ghaleb

Reputation: 5240

You can do it as follows:

OffsetDateTime utc = OffsetDateTime.now(ZoneOffset.UTC);
String sql_date = utc.format(DateTimeFormatter.ofPattern("MM/dd/yyyy")); //here, you can change the format of SQL date as you need

You would need to import the classes as follows:

import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;

Hope it helps.

Upvotes: 0

Related Questions