This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2020-05-06
Channels
- # announcements (5)
- # aws (28)
- # babashka (4)
- # beginners (163)
- # bristol-clojurians (2)
- # calva (4)
- # cider (18)
- # clj-kondo (30)
- # cljs-dev (28)
- # cljsrn (50)
- # clojure (96)
- # clojure-europe (25)
- # clojure-italy (6)
- # clojure-losangeles (1)
- # clojure-nl (4)
- # clojure-sweden (7)
- # clojure-uk (32)
- # clojurescript (39)
- # conjure (74)
- # cursive (12)
- # events (1)
- # fulcro (32)
- # helix (71)
- # jackdaw (2)
- # leiningen (10)
- # off-topic (14)
- # pathom (59)
- # rdf (7)
- # re-frame (6)
- # reitit (28)
- # ring (7)
- # shadow-cljs (207)
- # slack-help (2)
- # spacemacs (3)
- # specter (7)
- # sql (12)
- # tools-deps (14)
- # xtdb (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())
);
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"]
@U07TTE6RH Would you mind explaining what will be the benefit of using timestamp
over timestamptz
?
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.
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
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/
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.
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 😞