473,889 Members | 1,679 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Automatic Timestamp

Hello,

I am working with DB2 v9, and I am looking to create a TIMESTAMP
column in one of my tables that will automatically insert the current
date/time when I add a new row. Is there a way to do this by
specifying a generation formula, or must I create a trigger? Thank
you.

Jun 7 '07 #1
5 31330
Jeff Shantz wrote:
I am working with DB2 v9, and I am looking to create a TIMESTAMP
column in one of my tables that will automatically insert the current
date/time when I add a new row. Is there a way to do this by
specifying a generation formula, or must I create a trigger? Thank
you.
The easiest would be to use a default:

CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

Or create a before trigger:
CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS n FOR EACH ROW
SET c1 = CURRENT TIMESTAMP

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 7 '07 #2
Serge Rielau wrote:
>I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
The easiest would be to use a default:

CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)
By the way, what about removing "CURRENT TIMESTAMP" from the above ?

| CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)

The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value : "For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..
--
Konstantin Andreev.
Jun 7 '07 #3
Konstantin Andreev wrote:
Serge Rielau wrote:
The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
"For existing rows, a date corresponding to January 1, 0001, and a time
corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What
this crypto means ? My experimentation did not clear it..
Documentation is correct. Here is an example:

---------------------------- Example start -----------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT x.x.x
SQL authorization ID = xxxxxxxx
Local database alias = SAMPLE
C:\>db2 create table t (c1 int with default)
DB20000I The SQL command completed successfully.
C:\>db2 insert into t values default
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1
-----------
0

1 record(s) selected.
C:\>db2 alter table t add column c2 timestamp with default
DB20000I The SQL command completed successfully.
C:\>db2 insert into t values (1,default)
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1 C2
----------- --------------------------
0 0001-01-01-00.00.00.000000
1 2007-06-07-18.37.02.993000

2 record(s) selected.

---------------------------- Example end -----------------------------

Perhaps I should visit you and we can exchange experiences?
Jan M. Nelken
Jun 7 '07 #4
Jan M. Nelken wrote:
Konstantin Andreev wrote:
>The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
"For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..

Documentation is correct.
Correctness does not mean neither lucidity nor completeness :(
C:\>db2 create table t (c1 int with default)
C:\>db2 insert into t values default
C:\>db2 alter table t add column c2 timestamp with default
C:\>db2 insert into t values (1,default)
C:\>db2 select * from t

C1 C2
----------- --------------------------
0 0001-01-01-00.00.00.000000
1 2007-06-07-18.37.02.993000

Gotcha! The statement in question is about *added* columns, not about the columns the table was created with...
Thank you again, Jan.
--
Konstantin Andreev.
Jun 7 '07 #5
On 8 Juni, 00:06, Konstantin Andreev <feobalcaip...@ datatech.ru>
wrote:
Serge Rielau wrote:
I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
The easiest would be to use a default:
CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

By the way, what about removing "CURRENT TIMESTAMP" from the above ?

| CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)
[...]

Minor nitpick on the syntax used.

CREATE TABLE T(c1 TIMESTAMP DEFAULT CURRENT_TIMESTA MP)

is standard compliant

/Lennart

[...]
Jun 8 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2888
by: @$operamail$.com | last post by:
I have been trying to use a MySQL database with PHP having a date/time field set to timestamp(14). It keeps being set to a string of all zeroes (00000000000000). This happens evn if I use a query with a NULL value... ....the field is supposed to keep a record of modifications made to client records, so the TimeStamp value seemed to be the best fit. Is there sonmething I might be missing? Thanks for any help offered,
7
3677
by: svilen | last post by:
hello again. i'm now into using python instead of another language(s) for describing structures of data, including names, structure, type-checks, conversions, value-validations, metadata etc. And i have things to offer, and to request. And a lot of ideas, but who needs them.... here's an example (from type_struct.py):
4
1860
by: GitarJake | last post by:
Hello all, I am very new to ASP programming. My experience so far is limited to VBA in Windows based apps like Access, Excel etc. Is ASP event driven like Visual Basic? If so, can ASP cause the server time to be input into a field on a double click event?
2
2582
by: Filippo | last post by:
Hi, i have a problem about the CLIENT-SERVER architecture procedure. Well , i have an application in VB with ADO connection to a table in a database on a SQLSERVER 7.0 . Is possible to do that when a client updates a data in a field of my table , the SERVER communicates to all clients connected to my table that this data are updated , without the client do anything , for example without a client-timer to control the data in the server ???...
2
5187
by: rong.guo | last post by:
Hello Group, I am having a really weird problem... Can anyone tell the difference between Query 1 and Query 2 below? Why Query 2 excludes '2/28/2005'? Many thanks! create table a (ApplicationID int ,timestamp datetime)
2
4385
by: Russell Smith | last post by:
Timestamps support infinity. However if appears dates do not. When timestamps are cast to dates, there is no output. Is this an acceptable option or not? Below are a number of examples showing what I am experiencing. The last own shows how converting timestamps to dates and then ordering doesn't give you the order you want. Maybe you should just order by the timestamp to begin with. However Date does not understand infinity at all.
22
6437
by: Mal Ball | last post by:
I hope I have the right forum for this question. I have an existing Windows application which uses a SQL Server database and stored procedures. I am now developing a web application to use the same database. The original Update and Delete SP's all use a timestamp for concurreny checking. I am trying to use the same Update SP from my sqlDataSource but I keep getting the following error:
7
6125
by: JJ | last post by:
How do I set one field to have the updated timestamp, and another to have the created timestamp? I want to do this directly from code generated from DB Designer if possible?! JJ
3
26752
by: Soulless | last post by:
Hi, I am accessing a stored proc from within C# and the result of the proc populate a dataset. The problem is, the proc returns an int and a timestamp. I do not need the timestamp and wish it was not there, but it is and my code fails because I am not sure what I can send the timestamp to in C# .... right now I have a column in my dataset defined as string, but it doesn't not work.
0
11203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10896
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7999
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5830
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3257
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.