Introduction to PostgreSQL Timestamp
PostgreSQL timestamp is used to store date and time format data into the database; timestamp automatically updates the timestamp each time when the row was modified or inserted into the table. If, in some case, if time zone of the server changes, it will not affect on actual data that we have stored in the database. Timestamp data type storage size is 8 bytes to storing timestamp into the database; the timestamp data type is beneficial and important in PostgreSQL to store the date and time data into the database.
Syntax:
select now () :: timestamp;
select ‘date’ :: timestamp;
select ‘date: time’ :: timestamp;
select CURRENT_TIMESTAMP :: timestamp;
Below is the parameter description of the above syntax are as follows:
- Select: Select is used to select timestamp value in timestamp syntax. We can select any date or time value to display the timestamp format in PostgreSQL. We can select the current date as now and the current timestamp function in PostgreSQL.
- Timestamp: It is used to store date and time format data into the database; it is used to handle or store the date and time of data stored in the database. We can select date and time by using this data type.
- Now (): This function is used to select the current date and time in PostgreSQL. If we want to use the current date and time, then we have used a now function in PostgreSQL. Now function is more important to select the current date and time in PostgreSQL.
- Date and time: We can use any date and time to display the format of timestamp. If we have used the only date, then it will display a date and time.
- Current timestamp: This function is used to select the current date and time in PostgreSQL. If we want to use the current date and time, then we have to use a current timestamp function in PostgreSQL. The current timestamp function is more important to select the current date and time in PostgreSQL.
How does Timestamp Data Type work in PostgreSQL?
- We can use a timestamp data type with and without a time zone. If we want to use a without a time zone, we need to use a timestamp data type; if we want to use a time zone, we need to use a timestamp data type.
- Basically, timestamp data type divided into types with and without a time zone.
- Below is the function of the timestamp data type is as follows.
- Now ()
- Current_timestamp
- Timeofday ()
- Now function is used to select the current date and time in PostgreSQL. If we want to use the current date and time, then we need to use a now function in PostgreSQL. Now function is more important to select the current date and time in PostgreSQL.
Below figure shows the example of now function in PostgreSQL:
Code:
select now();
Output:
- The time of day function is used to select todays day, date and time format with timezone.
The below figure shows the example of a time of day function in PostgreSQL.
Code:
select timeofday();
Output:
- The current timestamp function is used to select the current date and time in PostgreSQL. If we want to use the current date and time, then we need to use a current timestamp function in PostgreSQL.
Code:
select Current_timestamp;
Output:
- The timestamp is not a time zone data type; it only stores time and date; in some case, if the time zone of the server changed, it will not affect on actual data that we have stored into the database.
- Timestamp data storage size is 8 bytes to store data into the database; timestamp data type is beneficial and important in PostgreSQL to store the data’s date and time data.
- A low value of timestamp data type is 4713 BC, and a higher value of timestamp data type in PostgreSQL is 294276 AD.
- The timestamp data type storage size is 8 byte.
Below example shows the size of timestamp data type in PostgreSQL:
Code:
SELECT typname as "datatype", typlen as "length of datatype" FROM pg_type WHERE typname like 'timestamp%';
Output:
Examples of PostgreSQL Timestamp
Given below are the examples:
Example #1
We are giving data type of timestamp at the time of the creation of the table.
In the below example, we have given the timestamp format to the date of the joining column.
Code:
CREATE TABLE Employee_Timestamp (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining timestamp NOT NULL);
Output:
Example #2
The below example shows add a column and assign a timestamp data type to the newly added column using alter command are as follows.
Code:
ALTER TABLE Employee_Timestamp ADD COLUMN Date_Of_Resignation timestamp;
Output:
Example #3
In the below example, we have used date using timestamp format are as follows. Using with and without time zone format.
- With time zone: Below example shows timestamp with the time zone in PostgreSQL.
Code:
select timestampz ‘2020-03-18’;
Output:
- Without time zone: Below example shows timestamp without time zone in PostgreSQL is as follows.
Code:
select timestamp '2020-03-18';
Output:
Conclusion
Timestamp uses 8 bytes storage space for storing data into the database; timestamp data type is beneficial in PostgreSQL to store the date and time data into the database. There are three types of timestamp data types in PostgreSQL, i.e. now (), timeofday () and Current_timestamp.
Recommended Articles
This is a guide to PostgreSQL Timestamp. Here we discuss the introductions, how to timestamp data type works in PostgreSQL? and examples. You may also have a look at the following articles to learn more –
