[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3642
  • Last Modified:

DB2 timestamp default

Hi all,

Is that possible to create a column with default of UTC timestamp?

instead of:
CREATE TABLE tbl (
      DT_WHEN TIMESTAMP DEFAULT current timestamp NOT NULL, .....

I tried
CREATE TABLE tbl (
      DT_WHEN TIMESTAMP DEFAULT(current  timestamp - current timezone) NOT NULL, .....

but it gives me an error.

Please help,

Gene
0
darrgyas
Asked:
darrgyas
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
Isn't the current timezone the default for current timestamp?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The CURRENT TIMEZONE special register is a integer value that contains the number of hours, minutes, and seconds that the CURRENT TIMESTAMP (and date, time registers) vary from UTC.
db2 => select current_timezone from sysibm.sysdummy1

1
--------
 -40000.

  1 record(s) selected.

IBM does tend to over-engineer a lot of stuff, and this is one of them.  The expanded value allows you to adjust the time even for an oddball time zone that varies by fractions of an hour.

Anyway, you can't do what you're trying to do.  IBM allows very limited calculations in a generating a default value.

Depending on your version of DB2, you CAN declare the column as TIMESTAMP WITH TIMEZONE.  Db2 will store the timestamp in UTC and maintain the timezone along with the value.

As far as I know, that feature isn't in DB2/LUW (yet).  But it is in the Z/OS version.


Kent
0
 
darrgyasAuthor Commented:
Thank you, that explains it
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now