PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – OffsetDateTime date object not getting stored in db the way date is set in object

I have a model which has startDateTime field which is storing DateTime in OffsetDateTime format.

startDateTime: 2023-07-25T04:40:46.143-08:00

However when we store the above object in our PostgreSQL db on GCP, it is getting stored in below format:
2023-07-25 12:40:46.143+00

It looks like it is adjusting the above object to UTC time.
But my requirement is, it sohuld store in the same format which is there in object and should not adjust to UTC time.

I explored the methods given here, but none of the methods is satisfying my requirement.

Can someone please suggest if there is a way to acheive this. Any help would be appreciated.

Code I used and input is startDateTime: 2023-07-25T12:40:46.143Z
and "timeZoneOffset": "UTC-08:00". I am converting input to expected OffsetDateTime based on timezoneOffset value.

 val actualDateTime: OffsetDateTime = OffsetDateTime.parse(startDateTime)
            val zoneOffset: ZoneOffset = ZoneOffset.of(timeZoneOffset.replace("UTC", ""))
            val expectedDateTime: OffsetDateTime = actualDateTime.withOffsetSameInstant(zoneOffset)
            return expectedDateTime.toString()

DDL:

CREATE TABLE IF NOT EXISTS TRANSACTION
(
    id                uuid                     DEFAULT,
    start_date_time   TIMESTAMP WITH TIME ZONE NOT NULL,
    end_date_time     TIMESTAMP WITH TIME ZONE NOT NULL,
    currency          VARCHAR(5)               NOT NULL,
    country           VARCHAR(50)              NOT NULL,
    created_at        TIMESTAMP WITH TIME ZONE NOT NULL,
    created_by        VARCHAR (255)            NOT NULL,
    startDateTime     TIMESTAMP WITH TIME ZONE NOT NULL
)
Tags:

2

Answers


  1. 0

    Your best practice is to think of time zone as a presentation attribute and the timestamp itself as an instant in time. If your business need requires that you preserve the timezone of the input field, then you must store that in another field outside of the timestamp.

    The type "timestamp with timezone" is used by postgresql only to interpret the timezone offset from an input string. That timezone data is not preserved by the database. The timestamp is always stored in UTC, and the time zone is immediately lost.

    Login or Signup to reply.
  2. 0

    [Postgres] is adjusting the above object to UTC time.

    Yes, that is how Postgres works. That is a feature, not a bug.

    For the type TIMESTAMP WITH TIME ZONE, any zone or offset info included with a submitted value is used to adjust “to UTC”, meaning an offset of zero hours-minutes-seconds from the temporal meridian of UTC. The Answer by phatfingers is correct.

    my requirement is … should not adjust to UTC time.

    If you want to record a moment, a specific moment on the timeline, while preserving the original offset in Postgres, you have these choices:

    • Use a textual type like TEXT or VARCHAR to record a string in standard ISO 8601 format. Such strings will be in chronological order when sorted alphabetically as long as you (a) use the same resolution (seconds, microseconds, etc.), and (b) avoid the use of the Z abbreviation of +00:00.
    • Store the date-time portion in a column of type TIMESTAMP WITHOUT TIME ZONE, along with storing the offset as text in standard IOS 8601 format or storing the offset as a signed number, perhaps a number of seconds.

    Sorting does not work with that second option. So you might need to denormalize your table, to redundantly store the same moment as a UTC value in an extra column of type TIMESTAMP WITH TIME ZONE.

    However, if I were on your team, I’d be asking if you truly need to keep that original offset.

    And be aware that doing date-time math to move into the past/future using offsets may be faulty as you would be ignoring changes made to the offset by the implicit time zone.

    Programming by intuition is risky. You really should study the documentation before using a data type.

    In particular, databases vary radically in their date-time handling capabilities and behaviors. The SQL standard does little more than mention the names of a several possible data types — and even that little they botched.

    So every database engine does their own thing. Be careful. Study the doc. Experiment and practice.

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

Back To Top
Search