Cannot select data with time conditions

[ZBernik]

Introduction

Cannot select data with time conditions.

To Reproduce

CREATE TABLE monitoring.db_availability (
  id SERIAL,
  hostname varchar(50),
  db_version varchar(30),
  databases varchar(100),
  port varchar(10),
  availability varchar(5),
  Last_update timestamp,
  PRIMARY KEY (id)
);

COPY monitoring.db_availability (id, hostname, db_version, databases, port, availability, last_update) FROM stdin;
18767   KLMTRLJHcP      Postgresql 13.5 dfgh        5432    UP      2024-07-29 11:07:22
18768   KLMTRLJHcP      Postgresql 13.5 hhjkk       5434    UP      2024-07-29 11:07:23
18769   KLMTRLJHcP      Postgresql 13.5 gghjhjkk   5432    UP      2024-07-29 11:07:22
18770   KLMTRLJHcP      Postgresql 13.5 gghhjk      5433    UP      2024-07-29 11:07:22
18771   KLMTRLJHcP      Postgresql 13.5 yyuhhn      5434    UP      2024-07-29 11:07:23
18772   KLMTRLJHcG      Postgresql 16.2 dfghhjh     5432    UP      2024-07-29 11:07:22
18773   KLMTRLJHrG      Postgresql 16.2 jhggghjj    5434    UP      2024-07-29 11:07:23
18774   KLMTRLJHrP      Postgresql 13.5 rttyyuuu    5435    UP      2024-07-29 11:07:23
18775   KLMTRLJHde      Postgresql 16.1 vbnmnm   5432    UP      2024-07-29 11:07:22
18776   KLMTRLJHdT      Postgresql 16.2 bgghjj       5432    UP      2024-07-29 11:07:22
18777   KLMTRLJHuT      Postgresql 16.2 bbnmm      5432    UP      2024-07-29 11:07:23
18778   KLMTRLJHiG      Postgresql 16.1 ghghjj        5434    UP      2024-07-29 11:07:23
18779   KLMTRLJHpT      Postgresql 16.2 ghjnjjjj      5434    UP      2024-07-29 11:07:23
18780   KLMTRLJHhT      Postgresql 16.2 ghjjj          5434    UP      2024-07-29 11:07:24
18781   KLMTRLJDgj      Postgresql 16.2 utrghhh     5432    UP      2024-07-29 11:07:23

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

SELECT hostname, db_version, databases, port, availability, to_char(last_update, 'YYYY/MM/DD HH24:MI:SS') as last_refresh, 'green'  as _sqlpage_color  FROM monitoring.db_availability where availability = 'UP' and age(now(), last_update) < '15 minutes';

SELECT hostname, db_version, databases, port, availability, to_char(last_update, 'YYYY/MM/DD HH24:MI:SS') as last_refresh FROM monitoring.db_availability where availability = 'UP' and last_update > NOW() - INTERVAL '15 minutes';

Actual behavior

IGNORE time condition: age(now(), last_update) < '15 minutes'; or

NOW() - INTERVAL '15 minutes';

Expected behavior

select data according where clausule

Version information

• OS: RH9
• Postgressql 16
• SQLPage - last version

[lovasoa]

Hello and welcome to SQLPage! are you sure the problem comes from SQLPage? This looks like a timezone problem, since you are importing data without timezone information.

You can debug that by selecting the current time and the time difference with stored timestamps in a table, you should quickly see where the issue comes from:

SELECT Last_update, now(), now()-last_update;

[ZBernik]

Hello, Ophir,

it works like a charm, thank you so much! I have made this changes.

dbamonitor=> \d monitoring.db_availability
                                      Table "monitoring.db_availability"
    Column    |           Type           | Collation | Nullable |           
        Default
--------------+--------------------------+-----------+----------+-----------
----------------------------------
id           | integer                  |           | not null | nextval('db
_availability_id_seq'::regclass)
hostname     | character varying(50)    |           |          |
db_version   | character varying(30)    |           |          |
databases    | character varying(100)   |           |          |
port         | character varying(10)    |           |          |
availability | character varying(5)     |           |          |
last_update  | timestamp with time zone |           |          |
Indexes:
    "db_availability_pkey" PRIMARY KEY, btree (id)

cat imp_csv_dbamonitor.sql
SET TIMEZONE to  'Europe/Prague';
COPY monitoring.db_availability(Hostname, DB_version, Databases, Port, 
Availability,Last_update) FROM '/var/lib/pgsql/log/db_availability_imp.csv' 
DELIMITER '|';

Index.sql
set timezone 'Europe/Prague';
select 'shell' as component, 300 as refresh;
select
    'hero'      as component,
    'Databases availability' as title,
TRUE        as reverse,
'http://10.1.4.69:8080/spcss.png' as image;
select 'table' as component, 1 as sort, 1 as search;
SELECT hostname, db_version, databases, port, availability, to_char(last_
update, 'YYYY/MM/DD HH24:MI:SS') as last_refresh,'red'  as _sqlpage_color 
FROM monitoring.db_availability where availability = 'DOWN' AND last_update 
> NOW() - INTERVAL '15 minutes';
SELECT hostname, db_version, databases, port, availability, to_char(last_
update, 'YYYY/MM/DD HH24:MI:SS') as last_refresh,'green'  as _sqlpage_color 
FROM monitoring.db_availability where availability = 'UP' and last_update > 
NOW() - INTERVAL '15 minutes';

Best regards,

Zdeněk

[lovasoa]

You can use sqlpage/on_connect.sql to set the timezone on connection, so that it will be used in all your requests.

See https://github.com/lovasoa/SQLpage/discussions/170