Saving a timestamp value from a given string value

Question

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"

I see that the "30-APR-96 05:30:00.00000000 AM" has 8 ending zero's while the error says [.ffffff] which is 6. Maybe it cannot interpret that? — NLxDoDge, Apr 13 '22 at 12:08
`Timestamp.valueOf()` requires a string in the format of `yyyy-[m]m-[d]d hh:mm:ss[.f...]` and nothing else. — XtremeBaumer, Apr 13 '22 at 12:28
I recommend you don’t use `SimpleDateFormat`, `Date` and `Timestamp`. Those classes are poorly designed and long outdated, the first in particular notoriously troublesome. Instead use `ZonedDateTime`, `DateTimeFormatter` and other classes from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Apr 13 '22 at 15:13
A `Timestamp` hasn’t got, as in cannot have a format. Also timestamps are not stored in the database in the format you mention. They are only displayed that way when you retrieve them. Does this answer your question? [Timestamp comes with .0 at the end](https://stackoverflow.com/questions/17545196/timestamp-comes-with-0-at-the-end). Does [this](https://stackoverflow.com/questions/19544067/how-to-format-a-java-sql-timestampyyyy-mm-dd-hhmmss-s-to-a-dateyyyy-mm-dd-h)? — Ole V.V., Apr 13 '22 at 15:18
For a timestamp the data type `timestamp with time zone` is recommended in SQL Your format seems to indicate that in your case `timestamp` without time zone is used? In the latter case pass a modern `LocalDateTime` to your JDBC driver or JPA implementation, and everyone will be happy (except when one day the timestamp is interpreted in the wrong time zone, but preventing that will require changing the table design). See [Dates with no time or timezone component in Java/MySQL](https://stackoverflow.com/questions/285553/dates-with-no-time-or-timezone-component-in-java-mysql). — Ole V.V., Apr 13 '22 at 15:22
(Despite the question title the most upvoted answer under the question I just linked to does treat timestamps with and without time zones.) — Ole V.V., Apr 13 '22 at 15:23
If you indispensably need an old-fashioned `Timestamp` object for a legacy API that you cannot change, get it as `Timestamp.from(ZonedDateTime.parse(str, DateTimeFormatter.ofPattern("EEE MMM dd HH:mm:ss zzz yyyy", Locale.ROOT)).toInstant())`. On my Java the resulting `Timestamp` printed as `1996-04-30 02:00:00.0` because my time zone was at UTC offset +02:00 in April 1996. — Ole V.V., Apr 13 '22 at 15:31

Basil Bourque · Answer 1 · 2022-04-13T16:05:22.093

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.

Saving a timestamp value from a given string value

1 Answers1

tl;dr

Avoid legacy date-time classes.

ISO 8601

java.time