sql 2020-05-06 | Slack Archive

jaime14:05:32

Hello, I'm using next.jdbc and it seems like it is returning different date than stored in the postgres database. Here is the result of executing select * from goals in sql (not jdbc)

id                                  |wallet_id                           |title|description|target_amount|target_date|installment_amount|created_at         |updated_at         |
------------------------------------|------------------------------------|-----|-----------|-------------|-----------|------------------|-------------------|-------------------|
3001be93-81a1-4a44-b5dc-3ed47485d0c9|968d8164-b864-441f-804b-7ade37ce112f|hey  |           |        20000| 2020-04-30|               500|2020-05-05 21:43:40|2020-05-05 21:43:40|
2f0072e0-17b9-4373-99a8-012eb4a12548|968d8164-b864-441f-804b-7ade37ce112f|hey  |           |        20000| 2020-04-30|               500|2020-05-05 22:22:29|2020-05-05 22:22:29|

However, when querying using (sql/query ds ["select * from goals"]) , I'm getting different result, notice that the created_at & updated_at have 4 hours difference than the above query.

[#:goals{:description nil,
         :wallet_id #uuid"968d8164-b864-441f-804b-7ade37ce112f",
         :installment_amount 500,
         :title "hey",
         :updated_at #inst"2020-05-05T17:43:40.863055000-00:00",
         :id #uuid"3001be93-81a1-4a44-b5dc-3ed47485d0c9",
         :target_date #inst"2020-04-29T20:00:00.000-00:00",
         :created_at #inst"2020-05-05T17:43:40.863055000-00:00",
         :target_amount 20000}
 #:goals{:description nil,
         :wallet_id #uuid"968d8164-b864-441f-804b-7ade37ce112f",
         :installment_amount 500,
         :title "hey",
         :updated_at #inst"2020-05-05T18:22:29.641452000-00:00",
         :id #uuid"2f0072e0-17b9-4373-99a8-012eb4a12548",
         :target_date #inst"2020-04-29T20:00:00.000-00:00",
         :created_at #inst"2020-05-05T18:22:29.641452000-00:00",
         :target_amount 20000}]

Here is the query that I use to create the goals table

create table goals (
    id uuid not null primary key,
    wallet_id uuid not null references wallets(id),
    title varchar not null,
    description text,
    target_amount int not null,
    target_date date not null,
    installment_amount int not null,
    created_at timestamp not null default timezone('utc', now()),
    updated_at timestamp not null default timezone('utc', now())
);

✅ 4

jaime15:05:09

After some google foo. It turns out that the JDBC driver will pick up the JVM timezone? So I just set the jvm timezone to 'UTC' using `

:jvm-opts ["-Duser.timezone=UTC"]

dcj15:05:33

Recommend you consider storing time as timestamptz also.

jaime16:05:14

@U07TTE6RH Would you mind explaining what will be the benefit of using timestamp over timestamptz ?

dcj16:05:36

I have found that using timestamptz the timezone is always contained within. Even though I always seek to store my time as UTC, I've had numerous instances where I ran into trouble, your JVM timezone example above being just one.

dcj16:05:35

this article and others like it explain this better than I can: https://medium.com/building-the-system/how-to-store-dates-and-times-in-postgresql-269bda8d6403

dcj16:05:42

The first question we need to answer here is about using timestamps with or without time zones from our applications. The answer is simple: always use_timestamps WITH time zones_. From: https://tapoueh.org/blog/2018/04/postgresql-data-types-date-timestamp-and-time-zones/

dcj16:05:48

Obviously you know your requirements best.... Having been burned numerous times, and having to painfully migrate my DB and/or recreate it from scratch, I recommend giving timestamptz serious consideration.

jaime16:05:41

Thank you for the references. I'll look into them 👍

jaime16:05:15

I was also looking into this topic lately. Hopefully these will give me more insight

seancorfield16:05:12

FWIW, we run all our servers set to UTC (even tho' they're on the East Coast), our JVMs set to UTC, and our database (Percona) set to UTC. If any part of that stack has a different TZ, you're just asking for trouble unfortunately. Timezones are brutal 😞

👍 4

dcj17:05:57

On the Clojure side, I keep my time in a ZonedDateTime. either tick or clojure.java-time have good support for that.

2020-05-06

Channels