0

I have to import a CVS file containing 120k rows. The first 6 columns contain the date and time separately, ie: year, month, day, hour, minutes, seconds.

I imported them into Java:

int year = 2016
int month = 5
int day = 23
int hour = 15
int minutes = 57
int seconds = 22

How can I convert this format to a format compatible with MySQL (DATETIME)?

I also have a GMT column, i.e. "GMT +0200". Can I convert this data too in a DATETIME?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Simon
  • 95
  • 1
  • 11
  • 1
    What is the [data type](https://dev.mysql.com/doc/refman/5.7/en/datetime.html) of the column? `DATETIME`? `TIMESTAMP`? – Basil Bourque Dec 23 '17 at 21:40

4 Answers4

3

One way you could do this is to build your string in this format:

yyyy-MM-DD HH:mm:ss

Then insert it.

INSERT INTO yourtable VALUES('2016-05-23 15:57:22')

Brian Laughlin
  • 91
  • 1
  • 6
2
String datetime = String.format("%s-%s-%s %s:%s:%s", year, month, day, hour, minutes, seconds);

That will produce you a DATETIME

Daniel Taub
  • 5,133
  • 7
  • 42
  • 72
2

tl;dr

myPreparedStatement.setObject( 
    … , 
    ZonedDateTime.of( 2016 , 5 , 23 , 15 , 57 , 22 , 0 , ZoneId.of( "Africa/Casablanca" ) )
) ;

Details

Using java.time classes with a driver supporting JDBC 4.2 or later for a MySQL 5.7 TIMESTAMP column…

Time zone

Determine your time zone intended for that date-time. A date-time has no exact meaning without a time zone. For example, noon in Paris France arrives much earlier than noon in Montréal Québec.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!). 

ZoneId z = ZoneId.of( "America/Montreal" );

ZonedDateTime & Instant

Use your inputs along with the time zone to instantiate a ZonedDateTime to represent a moment on the timeline.

ZonedDateTime zdt = ZonedDateTime.of( 2016 , 5 , 23 , 15 , 57 , 22 , 0 , z ) ;

Extract the same moment as a value in UTC.

Instant instant = zdt.toInstant() ;

Send to the database.

myPreparedStatement.setObject( … , instant ) ;

And retrieve.

Instant instant = myResultSet.getObject( … , Instant.class ) ;
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.

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • This is in every way the preferred way. Using data-time types rather than strings tells the reader what you are dealing with and it gives you data validation for free. The advantages can hardly be over-estimated. And most important, the code is a bit more easlily readable. – Ole V.V. Dec 24 '17 at 07:45
  • Thanks for the detailed answer. I see only now that i have a column in the csv where is stored a "GMT" value, i.e. "GMT +0200". Is this a converted time zone? Or not? – Simon Dec 24 '17 at 07:46
  • Yes it is, or more precisely, a time zone offset (in many time zones, the offset varies over the year and/or has been changed historically and is likely to be again in the future). You should probably find a way to parse it. – Ole V.V. Dec 24 '17 at 07:50
  • 1
    @Simon, `ZoneOffset.from(DateTimeFormatter.ofPattern("'GMT' xx").parse("GMT +0200"))` gives you a `ZoneOffset` object that you may use in constructing an `OffsetDateTime`. – Ole V.V. Dec 24 '17 at 08:20
  • 2
    @Simon Edit your Question to include all the pertinent details, and I'll update my Answer to show the use of `OffsetDateTime` class. And, in the future, try to be more thorough and better prepared with all your details worked out *before* posting here as Stack Overflow is meant to be more formal, more like Wikipedia, than a casual discussion board. And respond to my comment on your Question by editing your Question to document the data type of your column. – Basil Bourque Dec 24 '17 at 08:31
  • @Basil Bourque You're right, sorry. I edited my question just now. Thank you. – Simon Dec 24 '17 at 09:59
1

I (too) recommend you use java.time for this. Since you have an offset from UTC (for our purpose UTC and GMT can be considered equivalent), declare a formatter for parsing it:

private static final DateTimeFormatter gmtOffsetFormatter 
        = DateTimeFormatter.ofPattern("'GMT' xx");

Then create an OffsetDateTime object:

    ZoneOffset offset = ZoneOffset.from(gmtOffsetFormatter.parse(gmtOffsetString));
    OffsetDateTime dateTime 
            = OffsetDateTime.of(year, month, day, hour, minutes, seconds, 0, offset);
    System.out.println(dateTime);

Perhaps not surprisingly this prints

2016-05-23T15:57:22+02:00

As I understand, your database column has datatype datetime, which doesn’t allow the offset to be stored. You may want to consider a timestamp column instead. For the datetime, you at least need to know (or decide) at which offset or in which time zone to interpret the database values. To convert your date-times to your decided database offset:

    LocalDateTime dateTimeWithoutOffset = dateTime
            .withOffsetSameInstant(ZoneOffset.UTC)
            .toLocalDateTime();

I have taken UTC as a typical and recommended example. I am converting to LocalDateTime because I think this is the appropriate type to store into a datetime column, but this is not my home field, I might be mistaken. The snippet results in 2016-05-23T13:57:22, which is the UTC equivalent of our datetime. To store into the database, follow Basil Bourque’s answer. PreparedStatement.setObject() is happy to accept a LocalDateTime.

If you can change the column type to timestamp, store an Instant into it as also shown in Basil Bourque’s answer.

    Instant inst = dateTime.toInstant();

The result is 2016-05-23T13:57:22Z because Instant.toString() produces a string in UTC. The advantage of timestamp and Instant are that they denote unambiguous points on the time line, so neither you nor the people maintaining your code need worry any further about time zones or offsets.

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