Postgresql current_timestamp + Examples

In this Postgresql tutorial, we will learn about “Postgresql current_timestamp” using the current_timestamp function of the Postgresql database.

• postgresql current_timestamp in utc
• postgresql current_timestamp -1 day
• postgresql current_timestamp vs now
• postgresql current_timestamp milliseconds
• postgresql current_timestamp without timezone
• postgresql current_timestamp timezone
• postgresql current_timestamp format
• postgresql current_timestamp add day
• postgresql current_timestamp precision
• postgresql current_timestamp insert

Before the beginning, we need to know about the “What is current_timstamp function?.” Current_timestamp returns the current time and date with timezone.

Syntax:

CURRENT_TIMESTAMP(precision)

The Postgresql current_timstamp( ) takes one optional argument that is precision.

Precision: It specifies the number of digits in the fractional second precision of the returned time value. By default value is 6.

The following query shows the use of the current_timestamp function to get the date and time.

SELECT CURRENT_TIMESTAMP;

Postgresql current_timestamp in UTC

In Postgresql, we can get the current time and date in UTC ( Universal Time Coordinated ) by converting the Curren_timestamp function result to the Timestamp function result.

For casting or converting the value, we are going to use the type operator (::) of Postgresql.

Let’s run the below query to know, how it looks before casting to the timestamp for the time in UTC format.

SELECT current_timestamp;

In the above output, Current_timestamp function returns 2021-09-07 10:56:38.504086+05:30, which is in Indian Standard Time (IST).

Let’s show the same time in UTC format using the below query.

SELECT current_timestamp::timestamp at time zone 'UTC';

Now we can see the difference in the above output with UTC format, it returns 2021-09-07 16:34:02.778076+05:30.

:: timestamp at time zone ‘UTC”– This line converts the current timestamp value to a timestamp value in UTC date and timezone format.

Read: PostgreSQL CASE

Postgresql current_timestamp vs now

In Postgresql, There is no difference between current_timestamp and now functions, both perform the same operation.

But current_timestamp uses now() function internally.

If we don’t want to specify the precision parameter with the current_timestamp, then don’t put parentheses after the function.

If you don’t specify a column alias for a function call in a SQL statement, the alias is set to the function’s name by default.

Internally, the standard-SQL CURRENT_TIMESTAMP is implemented now(). Up to Postgres 9.6 that shows in the resulting column name, which was “now”, but changed to “current_timestamp” in Postgres 10.

Let’ see the result of the current_timestamp.

Select current_timestamp;

Now see the result of now() function.

Select now();

As we can see there is no difference in the result to current_timestamp and now.

Read: PostgreSQL WHERE with examples

Postgresql current_timestamp without timezone

In Postgresql, we need data and time without timezone using Current_timestamp function, then we always cast or convert it into timestamp value using Timestamp function.

Timestamp: This data type doesn’t have a timezone or it returns the date and time without a timezone.

Use the below query to cast or covert the current_timestamp value to get the current date and time without a timezone.

SELECT CURRENT_TIMESTAMP(0)::TIMESTAMP;

In the above output, it shows the only data and time (2021-09-07 11:23:49 ) without a timezone.

In Current_timestamp(0) function, we have given 0 as an argument to the current_timestamp, as a result, it will not show any fraction seconds after the date and time value.

Read: PostgreSQL INSERT Multiple Rows

Postgresql current_timestamp milliseconds

In Postgresql, to get milliseconds from the current_timestamp, we will use the data_part() function.

The DATE_PART function gives a portion of a DateTime based on its arguments. The subfield specified is extracted from the date, time, timestamp, and duration values.

Syntax

date_part(format_string, date_experession)

Data_part function accepts many format-string such as EPOCH, MILLENNIUM/MILLENNIUMS, MILLISECOND/MILLISECONDS, WEEK, etc.

It also takes different kinds of DateTime values as date_expression such as date, timestamp, now(), current_timestamp, etc.

Below is the demonstration of how to extract milliseconds from the current_timestamp.

select date_part( 'MILLISECONDS', current_timestamp );

In the above output, in the data_part function, we have provided two-parameter milliseconds and current_timestamp.

Read: PostgreSQL DROP COLUMN 

Postgresql current_timestamp timezone

In Postgresql, current_timestamp returns date and time with timezone, we can also set it different timezone using the below command.

Syntax

SET timezone = 'name_of_time_zone'

Let’s create the table named time_data.

CREATE TABLE time_data(time_zone_name VARCHAR, time_zone TIMESTAMPTZ)

Set timezone to ‘America/Los_Angeles’ and insert some data.

SET timezone = 'America/Los_Angeles';

INSERT INTO  time_data(time_zone_name,time_zone)values('America/Los_Angeles',current_timestamp(0)::timestamptz);

Now again set the timezone to America/New_York’.

SET timezone ='America/New_York';

INSERT INTO  time_data(time_zone_name,time_zone)values('America/New_York',current_timestamp(0)::timestamptz);

SELECT * FROM time_data;

Run the above query.

As we can see in the above output, Los_Angeles and New_York have different timezone, The difference between the time of Los_Angeles and New_York is 1 hour 17 minutes.

Read: PostgreSQL ALTER TABLE

Postgresql current_timestamp format

In Postgresql, the Current_timestamp function will return the current date as a ‘YYYY-MM-DD HH:MM:SS.US+TZ’ format.

So there is no option in current_timestamp to change the format of date and time.

Whenever we will use the current_timestamp, it is always going to show the result in default date and time format.

Read PostgreSQL Like With Examples

Postgresql current_timestamp add day and -1 day

In Postgresql, we can also add or subtract days from the current_timestamp function using the + and – operator with the interval data type.

Let’s find the current date and time using the current_timestamp and add one day to the current date and time.

SELECT CURRENT_TIMESTAMP(0);

As current date and time in the above output is 2021-09-07 11:54:26+05:30 with format ‘YYYY-MM-DD HH:MM:SS.US+TZ’.

Now add one day to the current date and time using the + interval ‘1’ day command.

SELECT CURRENT_TIMESTAMP(0) + interval '1' day as next_day;

As we can in the above output, we have added one day as next day 2021-09-08 12:01:29+05:30 using + interval ‘1’ day as next_day;

The current day changed from 2021-09-07 to 2021-09-08, we have added an interval of one to the current day.

Let’s subtract one day from the current_timestamp.

SELECT CURRENT_TIMESTAMP(0) - interval '1' day as pervious_day;

In the above output, we have subtracted one day as the previous day 2021-09-06 16:36:27+05:30 using – interval ‘1’ day as pervious_day ;

Read: PostgreSQL DATE Functions

Postgresql current_timestamp precision

In Postgresql, we are going to use Precision which is the optional argument to current_timestamp functions.

As I mentioned before, precision specifies the number of digits in the fractional second precision of the returned time value, by default value is 6.

If we provide zero precision to the current_timstamp, then it will not show the fractional seconds after the date and time.

Let’s understand with the example.

SELECT CURRENT_TIMESTAMP(0);

As we can see in the above output when we provide 0 precision to the current_timestamp function, it doesn’t show the fractional seconds between time (12:23:51) and timezone (+5:30).

Now let’s provide the 4 precision to the current_timestamp function.

SELECT CURRENT_TIMESTAMP(4);

In the above output in yellow color, that is the fraction seconds of 4 digit (9665) because of the current_timestamp function with 4 precision.

We can also reduce to 2 digits by providing precision as 2 to the current_timestamp function.

Read: PostgreSQL ADD COLUMN

Postgresql current_timestamp insert

In Postgresql, we are going to use the current_timestamp function with insert statement, In the below example, we will create a new table and insert some data with the date and time.

Let’s create the table named fruits_data.

CREATE TABLE fruits_data(fruits_id INT, fruits_name VARCHAR, purchased_date TIMESTAMP DEFAULT current_timestamp(0));

Insert the following records.

INSERT INTO fruits_data(fruits_id,fruits_name)values(1,'Apple');
INSERT INTO fruits_data(fruits_id,fruits_name)values(2,'Mango');
INSERT INTO fruits_data(fruits_id,fruits_name)values(3,'Apricot');
INSERT INTO fruits_data(fruits_id,fruits_name)values(4,'Banana - ripe');
INSERT INTO fruits_data(fruits_id,fruits_name)values(5,'Cucumber');

In the above output, while defining the schema of the table name fruits_data, a column named purchased_date has a data type timestamp with a default operation of current_timestamp(0), that will generate data for purchased_date automatically.

Now, we know How to use current_timestamp with the insert statement of Postgresql.

You may also like to read the following PostgreSQL tutorials.

• PostgreSQL vs SQL Server
• How to create database in PostgreSQL
• Postgresql auto increment
• Postgresql row_number
• Postgresql ilike case insensitive
• How to migrate from MySQL to Postgres
• Postgresql listen_addresses

So in this tutorial, we have learned about ” Postgresql current_timestamp” using the current_timestamp function with different timezones. We have covered the following topics.

• postgresql current_timestamp in utc
• postgresql current_timestamp -1 day
• postgresql current_timestamp vs now
• postgresql current_timestamp milliseconds
• postgresql current_timestamp without timezone
• postgresql current_timestamp timezone
• postgresql current_timestamp format
• postgresql current_timestamp add day
• postgresql current_timestamp precision
• postgresql current_timestamp insert