Question Re: UTC timestamp value issue with SQL anywhere 17 in Technology Q&A

UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694919 — Wed, 08 Nov 2023 01:47:33 GMT

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

Re: UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694879 — Wed, 08 Nov 2023 05:06:11 GMT

What is the date on the host server?

Re: UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694919 — Wed, 08 Nov 2023 05:46:03 GMT

host server date is EST time today date

Re: UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694879 — Wed, 08 Nov 2023 06:21:14 GMT

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

Re: UTC timestamp value issue with SQL anywhere 17

VolkerBarth — Wed, 08 Nov 2023 07:30:00 GMT

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.

Re: UTC timestamp value issue with SQL anywhere 17

VolkerBarth — Wed, 08 Nov 2023 10:40:09 GMT

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

Re: UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694919 — Thu, 09 Nov 2023 03:26:39 GMT

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

Re: UTC timestamp value issue with SQL anywhere 17

former_SQLA_member1694919 — Thu, 09 Nov 2023 03:29:33 GMT

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

Re: UTC timestamp value issue with SQL anywhere 17

VolkerBarth — Thu, 09 Nov 2023 04:36:47 GMT

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.