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 row was modified or inserted into the table. If in some case if time zone of server changes it will not effect on actual data that we have stored into the database. Timestamp data type storage size is 8 bytes to storing timestamp into the database, timestamp data type is very useful and important in PostgreSQL to store 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 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 current date as now and 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 date and time of data stored into the database. We can select date and time by using this data type.
- Now (): This function is used to select current date and time in PostgreSQL. If we want to use current date and time then we have use a now function in PostgreSQL. Now function is more important to select 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 only date then it will display a date and time.
- Current timestamp: This function is used to select current date and time in PostgreSQL. If we want to use current date and time then we have to use a current timestamp function in PostgreSQL. Current timestamp function is more important to select current date and time in PostgreSQL.
How Timestamp Data Type works in PostgreSQL?
- We can use a timestamp data type with and without time zone. If we want to use a without time zone then we need to use a timestamp data type, if we want to use with time zone then we need to use a timestamp data type.
- Basically timestamp data type divided into types with and without time zone.
- Below is the function of timestamp data type are as follows.
- Now ()
- Current_timestamp
- Timeofday ()
- Now function is used to select current date and time in PostgreSQL. If we want to use current date and time then we need to use a now function in PostgreSQL. Now function is more important to select current date and time in PostgreSQL.
Below figure shows the example of now function in PostgreSQL:
Code:
select now();
Output:
- Time of day function is used to select todays day, date and time format with timezone.
Below figure shows the example of time of day function in PostgreSQL.
Code:
select timeofday();
Output:
- Current timestamp function is used to select current date and time in PostgreSQL. If we want to use current date and time then we need to use a current timestamp function in PostgreSQL.
Code:
select Current_timestamp;
Output:
- Timestamp is not a time zone data type it only stores time and date, in some case if time zone of server changed it will not effect on actual data that we have stored into the database.
- Timestamp datatype storage size is 8 bytes to storing data into the database, timestamp data type is very useful and important in PostgreSQL to store date and time data into the database.
- Low value of timestamp data type is 4713 BC and 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 creation of table.
In below example we have given timestamp format to date of 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
Below example shows add column and assign 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 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 time zone in PostgreSQL.
Code:
select timestampz ‘2020-03-18’;
Output:
- Without time zone: Below example shows timestamp without time zone in PostgreSQL are 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 very useful in PostgreSQL to store date and time data into the database. There are three types of function of timestamp data type in PostgreSQL i.e. now (), timeofday () and Current_timestamp.
Recommended Articles
This is a guide to PostgreSQL Timestamp . Here we discuss the introductions, how timestamp data type works in PostgreSQL? and examples. You may also have a look at the following articles to learn more –
