EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

DB2 timestamp

Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
Home Data Science Data Science Tutorials DB2 Tutorial DB2 timestamp

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)

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 –

DB2 timestamp output 1

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 –

DB2 timestamp output 2

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
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More