0

I have a little function (used to be a part of a big one, but I've isolated the segment for this question):

CREATE OR REPLACE FUNCTION weird_date_issue_function() RETURNS INT AS
$BODY$
DECLARE
  deleted_count INT := 0;
BEGIN
  SELECT count(*)
  INTO deleted_count
  FROM user_verifications
  WHERE verified = FALSE AND now() - create_date > INTERVAL '1 second';
  RETURN deleted_count;
END;
$BODY$
LANGUAGE plpgsql;

The user_verification indeed contains an unverified entry older than 1 second (for testing purposes).

When I call select weird_date_issue_function(); from my IDE, it returns 1. But when I call it using jdbc.queryForObject("SELECT weird_date_issue_function()", Integer.class); it returns 0.

But wait, that's not all. If I comment out the AND now() - create_date > INTERVAL '1 second'; part, I get 1 in my application as well. What is going on here?

Selvedin Dokara
  • 21
  • 1
  • 2
    `now()` returns the time at the start of the **transaction**. So if you are running with autocommit off, that might be a "long time ago". Use one of the other time functions: https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT e.g. `clock_timestamp()` –  Jun 03 '16 at 19:40

1 Answers1

0

After a some searching and debugging, it seems that the timezones were the problem.

Both from my application and my IDE, the things like select now() would return the same, but as soon as I attempted any calculations, things went wrong.

By executing select now() - create_date from user_verifications, I would get the following two results (the first one being the correct one, from the IDE):

0 years 0 mons 0 days 0 hours 48 mins 40.564828 secs
0 years 0 mons 0 days -1 hours -11 mins -23.698618 secs

Since I'm in the CEST timezone, this smelled fishy. After finding PostgreSQL 9.2 JDBC driver uses client time zone?, it was enough for me to add

<property name="connectionInitSql" value="SET TIME ZONE 'CET'"/>

to my spring config until I find out more information about this issue.

Community
  • 1
  • 1
Selvedin Dokara
  • 21
  • 1