Redshift CURRENT_DATE Function

The CURRENT_DATE function in the REDSHIFT is used to return the date in the UTC time zone by default. The data type of the CURRENT_DATE function is the date, and the default format of the date stored is YYYY-MM-DD. This function can be used to keep track of the changes made to the tables of the cluster.

Although the default time zone for the returned value from the CURRENT_DATE function is UTC, it can be changed using other Redshift built-in functions. Similarly, some Redshift built-in functions can be used to extract the day, month, and year from the value returned from the CURRENT_DATE function.

In this blog, we will see how the Redshift CURRENT_DATE function can be used for different use cases.

Syntax of CURRENT_DATE

The syntax of the CURRENT_DATE function is much simpler than other built-in functions available for Redshift. The CURRENT_DATE function does not take any arguments and can be used along with a SELECT statement. Following is the syntax to get the start date of the current transaction running on Amazon Redshift.

SELECT CURRENT_DATE;

Get Current Date Using CURRENT_DATE Function

In order to get the current date in Amazon Redshift, you can use the CURRENT_DATE function. The CURRENT_DATE function returns the start date of the transaction instead of the start date of the current statement.

The following query will return the start date of the current transaction in the Amazon Redshift.

SELECT CURRENT_DATE;

Following will be the output of the CURRENT_DATE function in the Amazon Redshift cluster.

DATE
2022-06-15

The date returned from the CURRENT_DATE function will be in the UTC time zone. You need to tune it according to your time zone before concluding the results.

Change the Time Zone of CURRENT_DATE

As described earlier, the date returned by the CURRENT_DATE function will be in the UTC time zone by default. In order to get the date in your desired time zone, you need to change the time zone of the date from UTC to your preferred time zone.

In order to convert the time zone of the CURRENT_DATE function, we will use another built-in function CONVERT_TIMEZONE. This function will take the source time zone, target time zone, and source time, and it will convert the time into the target time zone.

The following query will generate the start date of the current transaction in your desired time zone.

SELECT CONVERT_TIMEZONE(‘UTC’, ‘GMT+5’, CURRENT_DATE);

convert_timezone
2022-06-15 19:00:00

When executed in Redshift, the previous query will first get the current date using the CURRENT_DATE function in the UTC time zone and then will convert the time zone of the date from UTC to GMT+5.

Getting Day, Month, and Year from CURRENT_DATE

The CURRENT_DATE function returns the date in YYYY-MM-DD format in the UTC time zone. You can extract the year, the month, and the day from the date using the EXTRACT function in Amazon Redshift.

In order to extract the year from the date using the CURRENT_DATE and EXTRACT function, execute the following query in the Redshift cluster:

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS YEAR;

YEAR
2022

The previous query will first get the start date of the current transaction in the UTC time zone, and then the EXTRACT function will extract the YEAR from that date.

Similarly, you can get the month from the current date by using the EXTRACT method. Executing the following query will extract the month from the date in the Redshift cluster:

SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS MONTH;

MONTH
6

To extract the date from the CURRENT_DATE function using the EXTRACT function, execute the following query inside the Redshift cluster:

SELECT EXTRACT(DAY FROM CURRENT_DATE) AS DAY;

DAY
16

The previous Redshift query will return the date today by extracting it from the CURRENT_DATE function.

You can also use the CURRENT_DATE function with CONVERT_TIMEZONE and EXTRACT functions to extract the date from the current date in your required time zone. Execute the following query in the Redshift cluster to extract the date from the current date in GMT+5 time zone.

SELECT CONVERT_TIMEZONE(‘UTC’, ‘GMT+5’, EXTRACT(DAY FROM CURRENT_DATE));

The previous query will first get the current date and then the EXTRACT function will extract the date from the CURRENT_DATE function. Then, the CONVERT_TIMEZONE function will change the time zone of the date from UTC to GMT+5.

Changing Date Format

The default format of the CURRENT_DATE function is YYYY-MM-DD, but you can always change the date format. The TO_CHAR function can be used to change the default format of the CURRENT_DATE function.

The following query will get the start date of the current transaction in your desired format.

SELECT TO_CHAR(CURRENT_DATE, ‘MM-DD-YYYY’);

to_char
06-16-2022

Conclusion

The CURRENT_DATE function in Redshift can be used to get the start date of the current transaction running on the Cluster. By default, this function returns the current date in UTC time zone in YYYY-MM-DD format. This function can be used with other functions to get the current date into your desired format and time zone. This article describes different cases of using the CURRENT_DATE function in conjunction with other functions related to time and date, such as CONVERT_TIMEZONE and EXTRACT.