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 Jul 11, 2020, at 3:17 AM, Deepika S Gowda <deepika.gs@gmail.com> 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.

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.

You’ll have to show the code generating the records. And the insert.
Your first post had times identical to the sub-second. We’re those artificial values? If so, can you show actual selected values which you believe arrived at approximately the same time but have both years? Include the sql.

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