Timestamp in Teradata with examples
What is Timestamp?
A timestamp is a current time of an event that is recorded by a computer. It is a combination of date and time values with small fraction of seconds. In the computer application, it is used to record the time of event occurrence. The events might be creating a file,inserting the record into a file, insert/update operation in the database,login/logout from your profile and so on.
Sample timestamp format with examples
| Timestamp Format | Example |
| dd MMM yyyy HH:mm:ss | 15 Mar 2020 11:20:35 |
| yyyy-mm-dd hh:mm:ss[.nnnnnnnnn] | 2020-04-23-03:23:34.987654321 |
Current_Timestamp in Teradata
Current_Timestamp is a build in function in Teradata that returns the current timestamp information from the Teradata database server.
|
1 |
SELECT CURRENT_TIMESTAMP; |
Format of Current timestamp in Teradata
Teradata returns the current timestamp in the following format ‘YYYY-MM-DDbHH:MI:SS.sssss’.
| Metacharacters | Description |
| YYYY | Display year in four digits (example : 2020) |
| MM | Display month in two digits (example : 12) |
| DD | Display date in two digits (example : 30) |
| b | Display white space in formatted timestamp |
| HH | Display hour in two digits (example : 11) |
| MM | Display minutes in two digits (example : 50) |
| SS | Display seconds in two digits (example : 55) |
| sssss | Display milliseconds in six digits(example : 130000) |
Get the Current timestamp without milliseconds in Teradata
The Current_timestamp function returns the timestamp value in the length of 26 characters( YYYY-MM-DDbHH:MI:SS.ssssss) that includes milliseconds also. If we don’t want milliseconds in the current timestamp, we can use like CURRENT_TIMESTAMP(0) in Teradata.
CURRENT_TIMESTAMP(0) returns the timestamp value in the length of 19 characters( YYYY-MM-DDbHH:MI:SS)
|
1 |
SELECT CURRENT_TIMESTAMP(0); |
Create table with Timestamp column in Teradata
Lets create the Transaction table with timestamp column and understand its usage in the real world.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE SET TABLE Banking_DB.Transaction,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Txn_id DECIMAL(38,0), User_Name VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC, Txn_Amout DECIMAL(38,0), Txn_Type VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC, cre_ts TIMESTAMP(0), time_row_updated TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss') UNIQUE PRIMARY INDEX(Txn_id); |
The Customers deposit/withdraw their amount from their Bank account. To capture those transaction details, we have created the Transaction table in the Banking database. Along with customer and transaction details, we need to capture the date and time at which the transaction has happened.
Teradata provides the TIMESTAMP datatype to store the timestamp information. We have included the cre_ts(Create timestamp) & time_row_updated columns with Timestamp datatype in Transaction table. Using these columns, we could store the timestamp details in Teradata.
Insert the values for Timestamp columns in Teradata
Assume that few customers made a transaction in a Bank, Lets insert those transaction details into the Transaction table.
|
1 2 3 4 5 |
INSERT INTO Banking_DB.Transaction VALUES (67437,'Alex',400,'Credit',CURRENT_TIMESTAMP(0),CURRENT_TIMESTAMP(0)); INSERT INTO Banking_DB.Transaction VALUES (82731,'Kevin',250,'Deposit',CURRENT_TIMESTAMP(0),CURRENT_TIMESTAMP(0)); |
Since we have specified the data type as TIMESTAMP(0) for the columns cre_ts & time_row_updated, we are allowed to insert only 19 characters( YYYY-MM-DDbHH:MI:SS).So we have mentioned the CURRENT_TIMESTAMP(0) to insert the timestamp values for those columns.
Output of Transaction table
Timestamp values has inserted without milliseconds in the Transaction table as below
Common error: Insert Failed – 7454 : DateTime field overflow.
If we wants to insert the timestamp values including milliseconds in Teradata, we need to specify the datatype as TIMESTAMP rather than TIMESTAMP(0). In the transaction table, we have defined the timestamp column as TIMESTAMP(0) as below.
|
1 2 |
cre_ts TIMESTAMP(0), time_row_updated TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss' |
If we try to insert the timestamp value with milliseconds (CURRENT_TIMESTAMP) for those columns, we will end up an error (Insert Failed – 7454 : DateTime field overflow) as below.
|
1 2 |
INSERT INTO Banking_DB.Transaction VALUES (32192,'Stephen',750,'Credit',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP); |
Recommended Articles
