DB2 timestamp

Introduction to DB2 timestamp

DB2 TIMESTAMP is also a function provided by IBM as well as the data type used for storing temporal values. The function is used for retrieving the timestamp value depending on the pair of parameter values that is supplied to it. DB2 TIMESTAMP is also the datatype available in DB2 Database Management System provided by IBM which helps to store the temporal values in the database. It is required to store the date and time as well as other information such as timestamp for various transactions that are happened in real-time events such as sales time and date in stores for every customer, date and time of multiple flights, buses, and trains facility and many other. In order to store these values, we can make use of timestamp data type in DB2. In this article, we will study the syntax and usage of TIMESTAMP datatype and function in DB2 along with the examples.

The TIMESTAMP datatype helps us to store date as well as time and contains a total of seven different parts stored in it which are year, month, and day for storing date and hour, minute, and seconds to store the time. There is one more thing called fractional seconds which can be stored to maintain an accurate and precise value of time. An additional optional temporal parameter for storing and specifying the time zone of that place where the data is being recorded.

Syntax

The syntax of TIMESTAMP datatype is as shown below –

Name of the column TIMESTAMP which mostly stores the value of date and time in yyyy-mm-dd-hh.mm.ss Format. However, we can have different types of formats for timestamps that are supported by DB2 and some of which also allow us to store the timezone-related information in the field.

The syntax of the TIMESTAMP function is as shown below –

TIMESTAMP (exp1, exp2)

The output of the TIMESTAMP function is the value of the TIMESTAMP datatype which varies depending on parameters passed to it. Where exp1 and exp2 are the expressions. Expression1 is a compulsory value while expression2 is optional in nature. Note that TIMESTAMP is the function available to use with graphic strings to represent the date, time, or timestamp only in Unicode databases because the graphic string provided in the argument is converted into a character string and then used for the execution of the function. There are certain rules to be followed while using this function and expression 2 argument that is supplied which are mentioned below –

• As expression2 is optional in nature, if it is not specified then the usage of only one argument then it should be any expression that gives the output return value in either DATE, character string provided it is not a CLOB value, or TIMESTAMP built-in datatypes.
• If the expression1 is specified and it is in character string format then it must be either a valid representation of timestamp or date in character string format, a 12-length character string which is generated by using the GENERATE_UNIQUE function or a 14 digit wide string of digits which specifies the valid time and date value in yyyyxxddhhmmss format where ss is the seconds, mm are minutes, hh is an hour, dd is the day, xx is month value and yyyy is the value of the year in the timestamp.
• If both expression1 and expression2 are together specified in arguments and expression2 is not an integer then it is necessary that expression1 must be a valid representation of date value in string or the value of the date in DATE datatype and expression2 should be a valid representation of time value in string format or a value of TIME datatype.
• If the type of the value specified in the second parameter of timestamp function is an integer then expression 1 should contain a value which is a valid string representation of a date or timestamp value, TIMESTAMP datatype value or DATE value, and expression 2 should be an integer value within the range of 0 to 12 which is used for representing the precise timestamp value.
• If we provide the values to both the expressions and the value of expression2 is not an integer value then the output is a TIMESTAMP(6) value in which we provide date as exxpression1 value and time in expression 2 value. The output contains the fractional second’s value set to zero in this case.
• If the expression2 value is an integer value supplied by us then the output timestamp value has the precision specified in the expression 2 value.
• If we mention only the value of expression1 then the output is the timestamp value of that expression.
• If we supply DATE value or valid string representation of DATE as expression 1 then the output is the date that has the time value set to midnight which is further cast to TIMESTAMP(0).
• If the expression1 is only provided in parameters and it is a string value then the output shows the TIMESTAMP(6) value without any information of time in it

Example of DB2 timestamp

Let us try to retrieve the current value of the timestamp in IBM DB2 by using the table named sysdummy1 in sysibm which is storing the current timestamp value in it by using the following query statement –

SELECT  CURRENT_TIMESTAMP myTimestampValue FROM sysibm.sysdummy1;

which gives the following output –

When only one parameter is passed as the date value in string format, for example, if we pass a certain date in the first expression as string value say 2021-04-03 in the following query statement –

SELECT TIMESTAMP("2021-04-03");

We get the following output after executing this query statement which contains no information of time in the timestamp datatype and format as shown below –

Conclusion

We can use the TIMESTAMP datatype provided by IBM in DB2 to store the temporal values including date, time, and timezone-related details. Further, IBM has also provided a function named TIMESTAMP() using which we can retrieve different timestamp values depending on the number and type of the parameter values supplied to it while execution. Out of the two parameters first is compulsory while the second is optional for the specification.

Recommended Articles

This is a guide to DB2 timestamp. Here we discuss the syntax and usage of TIMESTAMP datatype and function in DB2 along with the examples. You may also look at the following article to learn more –

1. What is DB2?
2. DB2 Interview Questions
3. Data Modelling Tools
4. MariaDB Select Database