Thread: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Deepika S Gowda
Date:
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".
Kindly help on this request
Regardss,
Deepika
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".
Kindly help on this request
Regardss,
Deepika
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Adrian Klaver
Date:
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
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Tim Cross
Date:
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
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Deepika S Gowda
Date:
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)
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)
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
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Rob Sargent
Date:
On Jul 11, 2020, at 3:17 AM, Deepika S Gowda <deepika.gs@gmail.com> wrote:
You’ll have to show the code generating the records. And the insert.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,DeepikaOn 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.
>
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.
Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp
From
Adrian Klaver
Date:
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