0

I am finding strange why database is having following result:

    my1db=# select now()::date;
            now
    ----------------------------
    2020-10-08 19:57:24.483647
    (1 row)

But here is the result of my other datbase:

    my2db=# SELECT now()::date;
        now
    ------------
    2020-10-08
    (1 row)

My 1st DB is not casting as shown from My 2nd DB

1STDB OS - RHEL 7 dbversion - PostgreSql 12.2
2NDDB OS - RHEL 7 dbversion - PostgreSql 12.1

May I know what am I missing? It is the reason why I am not getting correct results from my applications

Edit (*removed images):
1STDB

template1=# \dC date
                                      List of casts
         Source type         |         Target type         |   Function   |   Implicit?
-----------------------------+-----------------------------+--------------+---------------
 date                        | text                        | (with inout) | yes
 date                        | timestamp without time zone | timestamp    | yes
 date                        | timestamp with time zone    | timestamptz  | yes
 timestamp without time zone | date                        | date         | in assignment
 timestamp with time zone    | date                        | date         | in assignment
(5 rows)

template1=# select castfunc::regproc from pg_cast where casttarget = 'date'::regtype;
        castfunc
------------------------
 pg_catalog."timestamp"
 pg_catalog."timestamp"
 pg_catalog."timestamp"
(3 rows)


template1=# select castfunc::regproc, proname, proowner, prosrc, rolname from pg_cast, pg_proc, pg_authid where casttarget = 'date'::regtype and castfunc = pg_proc.oid and proowner = pg_authid.oid;
        castfunc        |  proname  | proowner |        prosrc         | rolname
------------------------+-----------+----------+-----------------------+---------
 pg_catalog."timestamp" | timestamp |       10 | date_timestamp        | pgadmin
 pg_catalog."timestamp" | timestamp |       10 | timestamptz_timestamp | pgadmin
 pg_catalog."timestamp" | timestamp |       10 | timestamp_scale       | pgadmin
(3 rows)

2NDDB - cast behaves normally 

psql (12.1)
Type "help" for help.

template1=# \dC date
                                      List of casts
         Source type         |         Target type         |  Function   |   Implicit?
-----------------------------+-----------------------------+-------------+---------------
 date                        | timestamp without time zone | timestamp   | yes
 date                        | timestamp with time zone    | timestamptz | yes
 timestamp without time zone | date                        | date        | in assignment
 timestamp with time zone    | date                        | date        | in assignment
(4 rows)

template1=# select castfunc::regproc from pg_cast where casttarget = 'date'::regtype;
    castfunc
-----------------
 pg_catalog.date
 pg_catalog.date
(2 rows)

template1=# select castfunc::regproc, proname, proowner, prosrc, rolname from pg_cast, pg_proc, pg_authid where casttarget = 'date'::regtype and castfunc = pg_proc.oid and proowner = pg_authid.oid;
    castfunc     | proname | proowner |      prosrc      | rolname
-----------------+---------+----------+------------------+----------
 pg_catalog.date | date    |       10 | timestamp_date   | postgres
 pg_catalog.date | date    |       10 | timestamptz_date | postgres
(2 rows)
iccy
  • 51
  • 2
  • 6
  • Why the cast at all? What's wrong with using `current_date`? –  Oct 08 '20 at 12:19
  • I have a field sendlog_dt | timestamp without time zone and I want to CAST it to date, sendlog_dt::date. As per checking casting it to ::date both server gives different results, its the same result using CAST(sendlog_dt AS date) – iccy Oct 08 '20 at 12:43
  • Then you should have shown us a different example. –  Oct 08 '20 at 12:44
  • My current default value for sendlog_dt | timestamp without time zone | 'now'::text::timestamp without time zone – iccy Oct 08 '20 at 12:45
  • Did someone add there own `now()` function to that database? `\df *.now` would be a start on tracking that down. Or has someone been played around with casting? What does `select '10/08/20 7:15'::timestamp::date;` return? ` – Adrian Klaver Oct 08 '20 at 14:14
  • I wonder if you have some kind of catalog corruption. Can you do a `pg_dump -s` from the affected database, replay it into a new database and have it reproduce the issue? – jjanes Oct 08 '20 at 15:10
  • @a_horse_with_no_name his example is fine as long as it actually does what he says. No point in bogging us down with table definitions when it is not needed to show the issue. – jjanes Oct 08 '20 at 15:11
  • Hi @AdrianKlave I updated my post with image – iccy Oct 09 '20 at 03:38
  • Please do not use images, do a copy/paste instead. I'm going to say someone/thing has been playing around with `CAST`. In `psql` what does `\dC date` show? – Adrian Klaver Oct 09 '20 at 17:32
  • Also the output from this:`select castfunc::regproc from pg_cast where casttarget = 'date'::regtype;` – Adrian Klaver Oct 09 '20 at 17:36
  • Hi @AdrianKlaver I updated the description for the post, since comments are limited, Thank you - but I already re-considered to re-install - alos 1STDB is enterprise, 2NDDB is not – iccy Oct 14 '20 at 05:10
  • You don't need to reinstall. The issue, I'm pretty sure, is the `date` cast that has ` (with inout)` as function. It is overriding the other `date` casts. As a superuser do:`select castfunc::regproc, proname, proowner, prosrc, rolname from pg_cast, pg_proc, pg_authid where casttarget = 'date'::regtype and castfunc = pg_proc.oid and proowner = pg_authid.oid;` That should give you more information about who installed it and what it is, with the intent of eventually dropping it. – Adrian Klaver Oct 14 '20 at 14:37
  • Hi @AdrianKlaver Thank you for the help, updated the description with the result. I do not know about `timestamp_scale` thats the only difference betwen 1ST and 2ND db. Hope you can give us inputs – iccy Oct 15 '20 at 05:35
  • Is the 1st database installed from EDB and if so what version(regular, Advanced server,etc) of their software? Also what extensions are installed. In `psql` `\dx` will show you them. – Adrian Klaver Oct 15 '20 at 14:12
  • Hi @AdrianKlaver, issue was addressed after consulting from pg support. Server is EDB and has `edb_redwood_date = on` config and not existing in server 2 since it is not in EDB version. Setting `edb_redwood_date = off` addressed the issue. Thanks for the help – iccy Oct 27 '20 at 12:20
  • That makes sense. In the future though I would lead with the information that the servers came from different sources. – Adrian Klaver Oct 27 '20 at 14:07

0 Answers0