Обсуждение: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

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

Deepika S Gowda <deepika.gs@gmail.com> writes:

> 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".
>

My bet would be you have some SQL statements which include a value for
'createddate', so the default is not being used.


-- 
Tim Cross



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.

>

On 7/11/20 2:17 AM, Deepika S Gowda wrote:
> 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.

You are going to have to expand on the above. In particular what is the 
'source DB'? Does this mean the data is coming from another database? Or 
more generally where is the data coming from? Bottom line is I don't see 
DEFAULT now() or localtimestamp randomly reverting back a year. There is 
something else in this process that is setting the timestamp back.

> 
> 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 
> <mailto: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 <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com