CODEWITHSUNDEEP
javatimestamp
Beginn
Beginn

Reputation: 1

Saving a timestamp value from a given string value

I am having a string value in format "Tue Apr 30 00:00:00 UTC 1996"; . I want this value to be converted into a timestamp field in "30-APR-96 05:30:00.000000000 AM"

What i tried here is

DateFormat formatter = new SimpleDateFormat("E MMM dd HH:mm:ss Z yyyy");
Date date = formatter.parse(str);
Timestamp ts = new Timestamp(date getTime());
SimpleDateFormat fo = new SimpleDateFormat("dd-MMM-yy hh:mm:ss.SSSSSSSSS a");
fo.format(ts);
//When i try here Timestamp.valueOf(fo.format(ts));

I am getting an exception Timestamp format must be yyyy-mm-dd hh:mm:ss[.ffffff]

In DB when i see i have the type as Timestamp and values are stored in "30-APR-96 05:30:00.000000000 AM"

Upvotes: 0

Views: 824

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338775

tl;dr

myPreparedStatement.setObject( 
    … , 
    ZonedDateTime
    .parse( 
        "Tue Apr 30 00:00:00 UTC 1996" , 
        DateTimeFormatter
        .ofPattern( "E MMM dd HH:mm:ss zzz uuuu" )
        .withLocale( Locale.US ) 
    ) 
    .toOffsetDateTime()
);

Avoid legacy date-time classes.

You are using terribly flawed date-time classes that were years ago supplanted by the modern java.time classes defined in JSR 310. Never use Date, Calendar, SimpleDateFormat, etc.

In particular, your input data uses a resolution of nanoseconds, while thé java.util.Date class is limited to milliseconds.

ISO 8601

Let me mention that data exchange should be using standard ISO 8601 formats for date-time values transmitted as text. The standard formats are concise, unambiguous, and easily read by both humans and machines.

Use custom or localized formats only for presentation to user, not for storage and data exchange.

So, "Tue Apr 30 00:00:00 UTC 1996" would be 1996-04-30T00:00:00Z where Z is short for +00:00, an offset from UTC of zero hours-minutes-seconds.

java.time

The java.time classes do support the nanoseconds resolution needed for your data inputs.

Define a formatting pattern to match the input. Specify a Locale to determine the human language and cultural norms used in translation.

Locale locale = new Locale( "en" , "IN" ) ;
DateTimeFormatter f = DateTimeFormatter.ofPattern( "E MMM dd HH:mm:ss zzz uuuu" ) ;

Parse your input.

ZonedDateTime zdt = ZonedDateTime.parse( "Tue Apr 30 00:00:00 UTC 1996" , f ) ;

See this code run live at IdeOne.com.

zdt.toString(): 1996-04-30T00:00Z[UTC]

Apparently you want to see this moment as it appears in the India 🇮🇳 time zone. Specify the desired time zone as a ZoneId object.

ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;

Apply the zone to get another ZonedDateTime object.

ZonedDateTime zdtKolkata = zdt.withZoneSameInstant( z ) ;

See this code run live at IdeOne.com.

zdtKolkata.toString(): 1996-04-30T05:30+05:30[Asia/Kolkata]

You asked for a java.sql.Timestamp object. That class is one of the terrible legacy classes to avoid. And it is unnecessary. JDBC 4.2 and later requires that a JDBC driver support java.time.

To write a moment to a database, we need to use OffsetDateTime rather than ZonedDateTime. The SQL standard does not specify time zones, only offsets.

And, there is no need for the India-specific value. Most databases saving into a column of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE will automatically adjust inputs into UTC (an offset of zero).

OffsetDateTime odt = zdt.toOffsetDateTime() ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Beware: Verify the data type of your database column. If you are using a column of a type akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE, you are using the wrong type. That type cannot represent a moment, a specific point on the timeline. Such a type has only a date with time of day, but lacks the context of an offset.

Upvotes: 1

Related Questions