Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp - Mailing list pgsql-general

From Deepika S Gowda
Subject Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
Date
Msg-id CADaTEeSb6_U57ZcvhOE8++CooeJLs19oeRPaBfEYh4Li+xpcyQ@mail.gmail.com
Whole thread Raw
In response to Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
List pgsql-general
Hi Adrian,

System timezone.
[postgres@pvodcdbst0001uk ~]$ timedatectl
      Local time: Fri 2020-07-10 15:44:37 BST
  Universal time: Fri 2020-07-10 14:44:37 UTC
        RTC time: Fri 2020-07-10 14:44:37
       Time zone: Europe/London (BST, +0100)

At DB level,
odc=# select now();
              now
-------------------------------
 2020-07-10 15:45:20.875835+01
(1 row)

odc=# select localtimestamp;
      localtimestamp
---------------------------
 2020-07-10 15:45:33.28083
(1 row)

===============
Createddate is loaded always with default value. its doesnt pick anything from source DB wrt this column value.

As said out of 3k records , sometimes 50 to 100 records it shows as 2019.

Regards,
Deepika


On Fri, Jul 10, 2020 at 7:39 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/10/20 7:03 AM, Deepika S Gowda wrote:
> Hi,
>
> On postgres 11.7 Master/Slave node, there is column named "createddate"
> with datatype "timestamp without time zone" with default value as "now()";
>
> Column Name | Date Type                 | Default value
> createddate |timestamp without time zone|Now()
>
>
> Issue: From the java application , data is getting loaded into this
> table where we expect column value should be today's date with
> timestamp( "2020-07-10 10:56:43.21"). But, out of 3K records, 100
> records are loaded as  "2019-07-10 10:56:43.21" (change in Year).
>
> What could be the issue? we tried changing the default value to
> "localtimestamp".

I would day the choices are:

1) A machine has it's clock set wrong.

2) The data is being loaded with a value for createdate that overrides
the DEFAULT.

>
> Kindly help on this request
>
> Regardss,
> Deepika


--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: "Ivan E. Panchenko"
Date:
Subject: Re: How to do phrase search?
Next
From: Rob Sargent
Date:
Subject: Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp