Convert Timestamp from db and Timezone column into RFC 3999 format

Question

Before I'll start - I've looked at those answers and didn't got the required answer:

I've currently read from a database 2 column:

created_at (a timestamp in which this column was created - for example, 2016-12-23 15:39:15)
timezone - the timezone of the timestamp (for example - GMT+01:00).

I need to convert this information into an RFC 3999 (e.g. - 2019-10-02 10:19:23-0800 representation of the time. however, I cannot figure out how to do so since from what I've saw OffsetDateTime expect a different format for the Timezone.

Another option might be to do it on the database level - but I couldn't figure out a way to do this as well...

EDIT:

If you have both the local time and the timezone you can use ZonedDateTime as follow:

final date = new LocalDateTime(...);
final String timezone = ...;
ZonedDateTime.of(date, ZoneOffset.of(zone));

Ok, I've got it. Actually I hope there's chance to refactor database to make one column with `timestamp with time zone` type — edwgiz, Feb 28 '20 at 18:19
Unfortunately not, this dB doesn’t owned by me, I’m only allowed to read from there — Yonatan Karp-Rudin, Feb 28 '20 at 18:52

score 1 · Answer 1 · answered Feb 28 '20 at 16:30

1

You may be looking for a simple type cast:

SELECT CAST (
          CAST (
             '2016-12-23 15:39:15' || ' ' || 'GMT+01:00'
             AS timestamp with time zone
          )
          AS text
       );

          text          
------------------------
 2016-12-23 17:39:15+01
(1 row)

answered Feb 28 '20 at 16:30

Laurenz Albe

209,280
17
206
263

In general, it looks good, my main concern is how will this impact performance since I'm executing my query on a huge dataset that continues to grow in almost 1.5M rows each day – Yonatan Karp-Rudin Feb 28 '20 at 17:18
That is rather cheap, four C function calls. – Laurenz Albe Feb 28 '20 at 20:24

score 1 · Accepted Answer · answered Feb 28 '20 at 18:12

...
    ResultSet rs = ...;
    LocalDateTime createdAt = rs.getObject("created_at", LocalDateTime.class);
    String timezoneStr = rs.getString("timezone");

    String createdAtRfc3999 = convert(createdAt, timezoneStr);
...
}

private static final DateTimeFormatter IN_ZONE_OFFSET_FORMATTER = DateTimeFormatter.ofPattern("O");
private static final DateTimeFormatter RFC3999_DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ssZ");

public static String convert(LocalDateTime datetime, String strOffset) {
    ZoneOffset zoneOffset = IN_ZONE_OFFSET_FORMATTER.parse(strOffset, ZoneOffset::from);
    return datetime.atOffset(zoneOffset).format(RFC3999_DATE_TIME_FORMATTER);
}

There's shouldn't be a problem to get created_at as LocalDateTime instance from the database whether via pure JDBC or via JPA

score 0 · Answer 3 · answered Mar 23 '20 at 08:47

You can do this in SQL:

select to_char(created_at at time zone timezone, 'yyyy-mm-dd hh24:mi:ss TZH:TZM')
from the_table

This assumes that timezone contains a valid time zone representation that is accepted by Postgres.

Convert Timestamp from db and Timezone column into RFC 3999 format

3 Answers3