can anyone help me suggest how can i convert snowflake ltz timestamp to given date using java?

Sample in snowflake ltz format: 1658443006.285000000

CodePudding user response：

Use smart objects, not dumb text

While I do not use Snowflake, a quick look at the documentation shows that their TIMESTAMP_LTZ type is akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

java.time.OffsetDateTime

So the appropriate class in Java is OffsetDateTime.

Assuming the Snowflake JDBC driver complies with JDBC 4.2 and later, you can extract an object rather than mere text from the database.

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

When writing to the database:

myPreparedStatement.setObject( … , odt ) ;

If you want to view this moment as seen in a particular time zone, apply a ZoneId to get a ZonedDateTime.

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Parsing text

You said:

Sample in snowflake ltz format: 1658443006.285000000

Can you cite the documentation on that? I see no such format in their page on TIMESTAMP_LTZ data type.

At any rate, I imagine the text you give represents a number of whole seconds since an epoch reference, along with a fractional second with nanosecond resolution.

Assuming the epoch reference is the first moment of 1970 as seen with an offset of zero hours-minutes-seconds from UTC (1970-01-01T00:00Z), we can easily parse that as a java.time.Instant object in Java. But I strongly urge you to use objects instead of this approach, as discussed above.

Split the input into two parts, two strings. Parse each a 64-bit long integer number.

To do the split, we must escape the FULL STOP character with a pair of REVERSE SOLIDUS characters.

String[] parts = "1658443006.285000000".split( "\\." ) ;
long seconds = Long.parseLong( parts [ 0 ] ) ;
long nanos = Long.parseLong( parts [ 1 ] ) ;

Use a factory method to instantiate a Instant object.

Instant instant = Instant.ofEpochSecond( seconds , nanos ) ;

See this code run live at Ideone.com.

instant.toString() → 2022-07-21T22:36:46.285Z

An Instant is in UTC, always in UTC, meaning an offset of zero.

If you want to view this moment as seen in a particular time zone, apply a ZoneId to get a ZonedDateTime.

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

zit.toString() → 2022-07-22T07:36:46.285 09:00[Asia/Tokyo]