Different results from postgreSQL function between JDBC & console

Question

I have a function in plpgsql which when I run it with same user on same table in same database, I get different results between JDBC call and psql or psycopg2.

SELECT id,name,
       date_deadline,
       g2j(date_deadline) AS converted_date 
FROM project_task 
WHERE NOT date_deadline ISNULL ;

result in psycopg2 and psql:

 id |               name               | date_deadline | converted_date 
----+----------------------------------+---------------+----------------
 21 | Document management              | 2019-10-29    | 1398-08-06
 26 | Create new components            | 2019-09-21    | 1398-06-29
 11 | You can set a deadline on a task | 2019-11-13    | 1398-08-21
 23 | User interface improvements      | 2020-04-08    | 1399-01-19
 24 | Planning and budget              | 2019-10-19    | 1398-07-26
 17 | Room 1: Decoration               | 2019-10-14    | 1398-07-21
 16 | Black Chairs for managers        | 2019-10-19    | 1398-07-26
 15 | Noise Reduction                  | 2019-10-24    | 1398-08-01
 31 | Unit Testing                     | 2019-02-16    | 1397-11-26
(9 rows)

Results from JDBC in pyCharm postgres console:

21  Document management 2019-10-29  1398-08-07
26  Create new components   2019-09-21  1398-06-30
11  You can set a deadline on a task    2019-11-13  1398-08-22
23  User interface improvements 2020-04-08  1399-01-20
24  Planning and budget 2019-10-19  1398-07-27
17  Room 1: Decoration  2019-10-14  1398-07-22
16  Black Chairs for managers   2019-10-19  1398-07-27
15  Noise Reduction 2019-10-24  1398-08-02
31  Unit Testing    2019-02-16  1397-11-27

The second result (JDBC) is correct.

           Column           |            Type             | Collation | Nullable |                 Default                  
----------------------------+-----------------------------+-----------+----------+------------------------------------------
 id                         | integer                     |           | not null | nextval('project_task_id_seq'::regclass)
 name                       | character varying           |           | not null | 
 date_deadline              | date                        |           |          |

Why am I getting this? &-|

I am using PostgreSQL 10.9 on Ubuntu

psql version is also 10.9

Function

-- Function: g2j(timestamp with time zone)

-- DROP FUNCTION g2j(timestamp with time zone);

CREATE OR REPLACE FUNCTION g2j(in_date timestamp with time zone)
  RETURNS character varying AS
$BODY$
DECLARE
y smallint;
aday smallint;
amonth smallint;
ayear smallint;
value smallint;
a1 char(4);
b1 char(2);
c1 char(2);
Tday smallint;
Tmonth smallint;
Tyear smallint;
temp smallint;
CabisehYear smallint;
TMonthEnd smallint;
numdays int;
now_day timestamp without time zone;
a timestamp without time zone;
Const_Date timestamp without time zone;
BEGIN
set datestyle to MDY;
Const_Date = cast('3/21/1921' as timestamp without time zone);
--if(length(cast(in_date as text))< 14 )then
--  in_date=in_date+time '01:30';
 --return in_date;
--end if;

numdays = DATE_PART('day',in_date - Const_Date);
aday = 1;
amonth = 1;
ayear = 1300;
CabisehYear =cast((numdays / 1461) as int);
numdays = numdays - CabisehYear * 1461;
Tyear = cast((numdays / 365) as int);
If Tyear = 4 then
Tyear = Tyear - 1;
end if;
numdays = numdays - Tyear * 365;
Tmonth =cast((numdays / 31) as int);
If (Tmonth > 6) then
Tmonth = 6;
end if;
numdays = numdays - Tmonth * 31;
TMonthEnd = 0;
If (numdays >= 30 And Tmonth = 6 ) then
TMonthEnd =cast((numdays / 30) as int);
If TMonthEnd >= 5 then
TMonthEnd = 5;
end if;
numdays = numdays - TMonthEnd * 30;
End if;
Tmonth = (TMonthEnd + Tmonth);
Tday = numdays;
Tyear = (Tyear + CabisehYear * 4);
ayear = (ayear + Tyear);
amonth = amonth + Tmonth;
aday = aday + Tday;

a1 = ayear;
b1 = amonth;
c1 = aday;


If length(b1) = 1 then
b1 = '0' || b1;
end if;
If length(c1) = 1 then
c1 = '0' || c1;
end if;
return a1 || '-' || b1 || '-' || c1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

What is the type of date_deadline? If it's a timestamp [without time zone] or a character type, the conversion to timestamp with time zone will involve adding the local time zone, which can be supplied by the client (through SET TIME ZONE) or else taken from the server's configuration. If there's a difference there, the conversion will have different results, and so will the subsequent calculation. — Jeroen Mostert
It's date type, I have not applied SET TIME ZONE and the code is run against same server. My first guess was also time zone, but it is a date field. — PMN
Your database driver code may do so implicitly, which would explain the differences you get from the JDBC access vs. the rest. The server's own time zone isn't the only thing. Per the docs, "the SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax. The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection." — Jeroen Mostert
Since date fields have no time zone information either (being dates), they, too, will necessarily have time zone information added when implicitly converted to timestamp with time zone as they're passed to the function. Is the with time zone in the function's declaration even correct? It doesn't look like it's supposed to be taking time zone offsets into account. — Jeroen Mostert
Thank you for the hints, I'll check the PGTZ and set time zone. I will also try using timestamp without time zone. Again thanks a lot. — PMN

Jeroen Mostert Jeroen Mostert · Accepted Answer · 2019-10-15T08:24:15

As the input parameter of the function is a timestamp with time zone, converting values that don't have time zone information (like date fields) involve adding the local time zone. Per the docs:

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:

The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

This means that clients using different libraries/drivers/environment settings can have different opinions on what the local time zone is, which in turn can affect query results.

As the calculation in this function is apparently intended to process dates and doesn't depend on the time zone at all, it should be changed to take a date or timestamp without time zone parameter.

Different results from postgreSQL function between JDBC & console

1 Answers