Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

we have declared the below column with table 1.

last_chg_utc datetime null default utc timestamp,

while inserting the row against table 1, default value is automatically populated for last_chg_utc column

The problem is default value is inserting with adding future date for every insert. For ex: today date is 08-11-2023 , default value is setting as 09-11-2023 for every insert it is incrementing the date value

Note: we are not setting any value against (last_chg_utc column) by default it is settting the value

kindly let us know why it is inserting the future date value by default.

how to resolve this automatic date increment issue

asked 08 Nov '23, 01:47

Sankar's gravatar image

Sankar
906611
accept rate: 0%

edited 08 Nov '23, 10:33

Volker%20Barth's gravatar image

Volker Barth
40.3k363551823

What is the date on the host server?

(08 Nov '23, 05:06) gchq

host server date is EST time today date

(08 Nov '23, 05:46) Sankar

If it's a Windows server run this in Powershell - it should output the date in UTC as you want it in SQL Anywhere - if it's not, then you need to look at the host server as your problem.

Get-Date -date (Get-Date).ToUniversalTime()-uformat %y%m%d%H%M%j

(08 Nov '23, 06:21) gchq

How do you "check" the timestamp values, i.e. with what query, and what is exactly displayed?

I'm asking as the display of dates and timestamps is affected by settings for options like date_format or timestamp_with_time_zone_format, and with SQL Anywehre 17, you can also let the database server "run in a different time zone".

What does this query reveal in DBISQL?

select connection_property('timestamp_with_time_zone_format'),
   connection_property('time_zone'), -- are TZs simulated?
   connection_property('time_zone_adjustment'),
   current timestamp,
   cast(current timestamp as timestamp with time zone),
   current utc timestamp

Here's my German-based result (i.e. CET, UTC+1):

YYYY-MM-DD HH:NN:SS.SSS+HH:NN;
;
60;
2023-11-08 13:23:58.241;
2023-11-08 13:23:58.241+01:00;
2023-11-08 12:23:58.241+00:00

Note, the 5th column (current timestamp displayed with time zone) returns a different time than the 6th (current utc timestamp) because it isn't normalized to UTC0.

permanent link

answered 08 Nov '23, 07:30

Volker%20Barth's gravatar image

Volker Barth
40.3k363551823
accept rate: 34%

converted 08 Nov '23, 07:30

FWIW, in the past, there have been some pitfalls with DEFAULT TIMESTAMP columns when the system clock was re-synchronized "back" according to changes due to DST or mere "inaccurate clocks" issues while the database server was running - as the DEFAULT IMESTAMP values seem to only increase monotonically while the database server is running and are prevented to "go back".

I don't know if those issues also applied to DEFAULT UTC TIMESTAMP and apply to your problem - but you might have a look here...

(08 Nov '23, 10:40) Volker Barth

How do you "check" the timestamp values, i.e. with what query, and what is exactly displayed?

After inserting a row into a table UTC timestamp column has been populated with the future date

permanent link

answered 09 Nov '23, 03:26

Sankar's gravatar image

Sankar
906611
accept rate: 0%

select connection_property('timestamp_with_time_zone_format'),
   connection_property('time_zone'), -- are TZs simulated?
   connection_property('time_zone_adjustment'),
   current timestamp,
   cast(current timestamp as timestamp with time zone),
   current utc timestamp
1)
yyyy-mm-dd hh:nn:ss.ssssss +hh:nn
2)
emptystring
3)
-300
4)
2023-11-09 03:21:52.000
5)
2023-11-09 03:21:52.000000 -05:00
6)
2023-11-09 08:21:52.000000 +00:00
permanent link

answered 09 Nov '23, 03:29

Sankar's gravatar image

Sankar
906611
accept rate: 0%

edited 09 Nov '23, 04:29

Volker%20Barth's gravatar image

Volker Barth
40.3k363551823

FWIW, I have formatted your code, you can do that itself via the CODE button or by including it with a pre tag pair...

That being said, do these values fit for your time zone? And do newly inserted/updated rows with DEFAULT UTC TIMESTAMP get higher values than "SELECT current utc timestamp" reveals?

If so, what is the value of option default_timestamp_increment, as that defines how defaults are adapted/incremented when they might otherwise lead to identical values in columns with DEFAULT (UTC) TIMESTAMP.

(09 Nov '23, 04:36) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×7

question asked: 08 Nov '23, 01:47

question was seen: 411 times

last updated: 09 Nov '23, 04:37