What Is the Mysterious 'Timestamp' Datatype in Sybase

What is the mysterious 'timestamp' datatype in Sybase?

What the heck is a timestamp?

The timestamp datatype is defined as

varbinary(8) null

Does it have any relation at all to time or date?

No. The name was poorly chosen.

Can I convert it to a datetime?

No.

If its not a time or a date, what do you use it for?

Each time a row with a timestamp column is inserted or updated, the timestamp column is updated automatically. Note that there are actually two kinds of timestamps. TIMESTAMP and CURRENT TIMESTAMP. The difference is that CURRENT TIMESTAMP is only set on insert.

The Sybase documentation stopped there leaving me wondering why the f*rainbow!*k anyone would ever use the datatype timestamp. Happily, I found some other discussions and deduced its used when implementing optimistic concurrency control.

Concurrency control is a method of ensuring that multiple transactions can run at/around the same time and still result in correct data. Optimistic concurrency control is a concurrency control method that assumes multiple transactions can complete without interfering with each other. Ie no locking is required. Wikipedia describes the following algorithm:

1. Record a date/time marking when the transaction starts
2. Read/update data
3. Check if another transaction modified the data
4. Commit or rollback

Sybase's timestamp datatype could be used in steps 1 and 3 of this algorithm instead of using a date/time. But it doesn't seem to me like it saves you much work over using a datetime datatype. I suppose it might perform better.

How to Copy a Timestamp Datatype

The Sybase (now SAP) replication server (SRS) can replicate timestamp values between Sybase/SAP ASE tables, ie, the SRS maintuser can insert explicit values into a column of type timestamp.

How is this possible? There are a few requirements:

• the user performing the insert (to the timestamp column) must have the replication_role role (and it has to be active)
• you need to issue the set timestamp_insert on command (NOTE: this will generate an error if your user doesn't have replication_role)
• you need to explicitly list the target table's columns in the insert statement

Setup:

exec sp_displaylogin
go
...
Configured Authorization:
        ...
        replication_role (default ON)      <<<=== verify role assigned and active
        ...

create table EX_EMPLOYEE
(NAME_X                 varchar(10)     NULL
,RECORD_TIME_STAMP      timestamp       NULL
)
go

insert into EX_EMPLOYEE (NAME_X) values ('Larry')
insert into EX_EMPLOYEE (NAME_X) values ('Mo')
insert into EX_EMPLOYEE (NAME_X) values ('Curly')
go

select * from EX_EMPLOYEE
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec4304fa
 Mo         0x00000000ec4304fd
 Curly      0x00000000ec430501

select * into EX_EMPLOYEE_T FROM EX_EMPLOYEE where 1=2
go

Now for some insert tests ...

-- haven't issued the 'set timestamp_insert on' commmand, yet

insert into EX_EMPLOYEE_T
select * from EX_EMPLOYEE
go

Warning: A non-null value cannot be inserted into a TIMESTAMP column by the user. The database timestamp value has been inserted into the TIMESTAMP field instead.

-- received the *WARNING*, ie, rows are inserted but they receive new timestamp values

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec430548       <<<=== different from what's in EX_EMPLOYEE
 Mo         0x00000000ec43054a       <<<=== different from what's in EX_EMPLOYEE
 Curly      0x00000000ec43054c       <<<=== different from what's in EX_EMPLOYEE

-- enable direct insert of timestamp values

set timestamp_insert on
go

truncate table EX_EMPLOYEE_T
go

-- w/out explicitly listing target columns ...

insert into EX_EMPLOYEE_T
select * from EX_EMPLOYEE
go

-- no warning message is generated, insert succeeds, but new timestamp values are generated

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec430555       <<<=== different from what's in EX_EMPLOYEE
 Mo         0x00000000ec430557       <<<=== different from what's in EX_EMPLOYEE
 Curly      0x00000000ec430559       <<<=== different from what's in EX_EMPLOYEE

truncate table EX_EMPLOYEE_T
go

-- this time we'll explicitly list the target table's columns ...

insert into EX_EMPLOYEE_T (NAME_X, RECORD_TIME_STAMP)
select * from EX_EMPLOYEE
go

-- and now we see the timestamp values copied from the source

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec4304fa       <<<=== same as what's in EX_EMPLOYEE
 Mo         0x00000000ec4304fd       <<<=== same as what's in EX_EMPLOYEE
 Curly      0x00000000ec430501       <<<=== same as what's in EX_EMPLOYEE

Above was tested on a ASE 15.7 SP138 dataserver.

Create a timestamp from seperate date and time int

I tried some options aswell, and this one seems to work for me:

CONVERT(
   VARCHAR,
   DATEADD(
      DAY,
      time_captured.end_date,
      `DEC 31 1971`
   ),
   104
) || `` || RIGHT(
   `00` || CONVERT(
      VARCHAR,
      FLOOR(time_captured.end_time/60)
   ),
   2
) || `:` || RIGHT(
   `00` + CONVERT(
      VARCHAR,
      time_captured.end_time%60
   ),
   2
) || `:00` >= CONVERT(
   VARCHAR,
   DATEADD(
      DAY,
      sp.start_date,
      `DEC 31 1971`
   ),
   104
) || `` || RIGHT(
   `00` || CONVERT(
      VARCHAR,
      FLOOR(sp.start_time/60)
   ),
   2
) || `:` || RIGHT(
   `00` + CONVERT(
      VARCHAR,
      sp.start_time%60
   ),
   2
) || `:00`

The way this database is made is quite weird. Thanks for all your answers!

JDBC asking timestamps from Sybase offset by an hour?

Problem was resolved by updating the JDBC database driver from build 26502 to 27361

To find out the version of your driver, do

java -jar jconn4.jar

Old version output this as the first row:

jConnect (TM) for JDBC(TM)/7.00(Build 26502)/P/EBF17993/JDK16/Thu Jun  3  3:09:09 2010

New version output this:

jConnect (TM) for JDBC(TM)/7.07 SP139 (Build 27361)/P/EBF27161/JDK 1.6.0/jdbcmain/OPT/Thu Jul 27 02:39:00 PDT 2017

---

Related Topics