How are Postgres `time` columns being cast to Date type on SELECT?

[theronic]

My GMT is +2. It seems that the library is offsetting times without time zones even when I store UTC times.

Given this Postgres table,

create table schedules (
  time_of_day time without time zone not null
)

I can execute INSERT schedules (time_of_day) VALUES (time '09:00:00') and retrieve the 09:00:00 time with a select query in psql:

> select time_of_day from schedules;
time_of_day 
-------------
 09:00:00
(1 row)

But when I retrieve this query with a fetch from clojure.jdbc, this comes back: :time_of_day #inst "1970-01-01T07:00:00.000-00:00" where the time is 07:00:00.

Am I doing something wrong? Is there a way to avoid this?

[niwinz]

Hi @pate You are not wrong, this is strange behavior, but using time is discouraged, you probably want to use just timestamptz instead of time without timezone.

This is happens because java does not have a way to represent the time without date (as far as I know), and if you store only time info in postgresql when it is retrieved, no date information available so the epoch time is used.

You can avoid that just casting it to text in the query: select time_of_day::text from schedules and you will receive the exact time representation on string.

[theronic]

I managed to get it working by using type time with time zone and inserting this value: time with time zone '09:00:00+00', which is then retrieved as #inst "1970-01-01T09:00:00.000-00:00". I tried timestamptz (which is an alias for timestamp with time zone), but then I have to mangle a 1970 date in the database and I can't query in the database for all schedules where time_of_day > some-time without applying some more functions in my SELECT query.