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 FormatExample
dd MMM yyyy HH:mm:ss15 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.

Get the current timestamp in Teradata
Get the current timestamp in Teradata

Format of Current timestamp in Teradata

Teradata returns the current timestamp in the following format ‘YYYY-MM-DDbHH:MI:SS.sssss’.

MetacharactersDescription
YYYYDisplay year in four digits (example : 2020)
MMDisplay month in two digits (example : 12)
DDDisplay date in two digits (example : 30)
bDisplay white space in formatted timestamp
HHDisplay hour in two digits (example : 11)
MMDisplay minutes in two digits (example : 50)
SSDisplay seconds in two digits (example : 55)
sssssDisplay 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)

Current_Timestamp(0) in Teradata
Current_Timestamp(0) in Teradata

Create table with Timestamp column in Teradata

Lets create the Transaction table with timestamp column and understand its usage in the real world.

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.

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

Timestamp columns in Teradata table
Timestamp columns in Teradata table

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.

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.

Teradata Datetime filed overflow error with code 7454
Teradata Datetime filed overflow error with code 7454