Twitter API Question posted in
The official Twitter API documentation can be found here.

Converting Twitter4J Date to SQL Date – Twitter API

I have this date format coming from Twitter API: Wed Oct 11 17:30:20 CEST 2017

In Java SQL I need this date format: 2017-10-11 17:30:20

How can i convert the date format to make it possible to insert into database?

This i my code:

Date createdAt = message.getCreatedAt();
Tags:

2

Answers


  1. 0

    Well, it seems getCreatedAt() method returns a java.util.Date object, so if you need to instantiate a java.sql.Date instance to save on your database, you can simply do this:

    Date createdAt = message.getCreatedAt();
java.sql.Date sqlCreatedAt = new java.sql.Date(createdAt.getTime())

    http://twitter4j.org/javadoc/twitter4j/Status.html#getCreatedAt–

    Login or Signup to reply.
  2. 0

    tl;dr

    Use objects, not strings.

    myPreparedStatement.setObject( 
    … , 
    ZonedDateTime.parse( 
        "Wed Oct 11 17:30:20 CEST 2017" , 
        DateTimeFormatter.ofPattern( "EEE MMM dd HH:mm:ss z uuuu" , Locale.US ) 
    ).toInstant() 
)

    java.time

    With JDBC 4.2 and later, you can directly exchange java.time objects with the database. No need to mess about with mere strings nor the troublesome old legacy date-time classes such as java.sql.Date/java.sql.Timestamp.

    Define a formatting pattern for your input. Specify a Locale to indicate the human language needed for translating the name of the day and name of the month.

    String input = "Wed Oct 11 17:30:20 CEST 2017";
DateTimeFormatter f = DateTimeFormatter.ofPattern( "EEE MMM dd HH:mm:ss z uuuu" , Locale.US );

    Parse as a ZonedDateTime given that your input includes a hint about the time zone. Note that your CEST is not actually a time zone, but ZonedDateTime will take a guess at interpreting. Such 3-4 letter pseudo-zones give a hint about the time zone, but are not standardized and are not even unique(!). This, along with the rest of your input string, is a terrible choice of date-time formats. Whenever possible, use standard ISO 8601 formats instead.

    Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. 

    ZonedDateTime zdt = ZonedDateTime.parse( input , f );

    Generally best to work in UTC. Extract a Instant object. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). 

    Instant instant = zdt.toInstant() ;

    Pass that to your database via JDBC driver supporting JDBC 4.2 or later.

    myPreparedStatement.setObject( … , instant ) ;

    Retrieval:

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

    If your database column is of the type TIMESTAMP WITHOUT TIME ZONE rather than TIMESTAMP WITH TIME ZONE, then use a LocalDateTime object rather than a ZonedDateTime/Instant. A LocalDateTime object lacks any concept of time zone or offset-from-UTC.

    LocalDateTime ldt = zdt.toLocalDateTime() ;
myPreparedStatement.setObject( … , ldt ) ;

    Legacy java.util.Date

    If your Twitter call actually returned a java.util.Date object rather than a String, convert to an Instant and follow the test of the steps seen above.

    Instant instant = myJavaUtilDate.toInstant() ;  // Convert from troublesome legacy `Date` class to modern `Instant` class.

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search