Question & Answer
Question
Why does CURRENT TIMESTAMP() function insert values ONLY up to milliseconds when used on Windows Platforms?
Cause
Here is an example which shows the usage of the CURRENT TMESTAMP() function on both UNIX and Windows and the difference between the output:
ON Linux / UNIX platforms:
- $db2 "create table timestmp(col1 timestamp)"
DB20000I The SQL command completed successfully.
$db2 "insert into timestmp values (CURRENT TIMESTAMP)"
DB20000I The SQL command completed successfully.
$db2 "select * from timestmp"
COL1
--------------------------
2010-02-16-15.04.52.888664
ON Windows platform:
- $db2 "create table timestmp(col1 timestamp)"
DB20000I The SQL command completed successfully.
$db2 "insert into timestmp values (CURRENT TIMESTAMP)"
DB20000I The SQL command completed successfully.
$db2 "select * from timestmp"
COL1
--------------------------
2010-02-16-15.10.54.015000
Answer
DB2 is working as designed. The cause of the discrepancy is due to limitation in the Windows Platform.
This restriction is documented in the db2diag tool command syntax, the link for which is provided in the Related URL section. Here is a reproduction of the timestamp and timezone information:
timestamp | ts
Time stamp. This field can be divided into its constituent fields: %tsyear, %tsmonth, %tsday, %tshour, %tsmin (minute), %tssec (second), %tsmsec (microsecond for UNIX® operating systems, millisecond for Windows® operating systems).
timezone | tz
Number of minutes difference from UTC (Universal Coordinated Time). For example, -300 is Eastern Time.
Following workaround can be used to avoid this.
Example.1
db2 "values (TIMESTAMP(GENERATE_UNIQUE()) + CURRENT TIMEZONE)"
Example.2
db2 "SELECT (TIMESTAMP(GENERATE_UNIQUE()) + CURRENT TIMEZONE) FROM SYSIBM.SYSDUMMY1"
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;10.1;10.5;11.1","Edition":"Enterprise Server;Express;Personal;Personal Developer's;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21421243