Overview of Time and Date Functions

The time and date functions of DWS are used to add or subtract dates, obtain the current time, format date display, and extract the year, month, and day. These functions are widely used in data analysis, schedule management, and financial reports, helping you process data more efficiently and accurately.

**Table 1** Time and date functions
Type	Function	Description
Basic time and date functions	day(date \| timestamp with time zone)	Returns the day of a given date or time.
	weekday(date \| timestamp with time zone)	Returns the week of a given date or time. Monday is the start day of a week.
	week(date[, mode])	Returns the week number in the year of the specified datetime.
	month(date \| timestamp with time zone)	Returns the month of a given date or time.
	quarter(date \| timestamp with time zone)	Returns the quarter to which the date or time belongs.
	year(date \| timestamp with time zone)	Returns the year of a given date or time.
	yearweek(date \| timestamp with time zone[, mode])	Returns the year and the week number in the year corresponding to a given date or time.
	dayofweek(date \| timestamp with time zone)	Returns the week of a given date or time. Sunday is the start day of a week.
	dayofmonth(date \| timestamp with time zone)	Returns the number of days in the month of a given date or time.
	dayofyear(date \| timestamp with time zone)	Returns the day of a given date or time in the year.
	weekofyear(date \| timestamp with time zone)	Returns the week number of a given date or time in the year. It is equivalent to week(date, 3).
	makedate(year, dayofyear)	Creates a date based on the given year and day of the year.
	maketime(hour, minute, second)	Generates a time value based on the given hour, minute, and second.
	hour(timestamp with time zone)	Returns the hour value in a timestamp.
	minute(timestamp with time zone)	Returns the minute value in the time.
	second(timestamp with time zone)	Returns the second value in a timestamp.
	microsecond(timestamp with time zone)	Returns the microsecond value in the time.
	from_unixtime(unix_timestamp[,format])	Converts a Unix timestamp to the datetime type when the format string is set to the default value.
	unix_timestamp([timestamp with time zone])	Converts a date or time to a Unix timestamp (that is, returns the total number of seconds that have elapsed since January 1, 1970 00:00:00 UTC).
	now([fsp])	Returns the start date and time of the current transaction.
	last_day(d)	Returns the date or time of the last day of the month that contains date.
	next_day(x,y)	Returns the earliest date that is later than its first argument (x), and that falls on the day of the week that its second argument (y) specifies.
	from_days(days)	Returns the date value based on the given number of days.
	to_days(timestamp)	Returns the number of days since the year 0 based on the specified date.
Time and date calculation functions	age(timestamp, timestamp)	Subtracts arguments and returns the difference.
	age(timestamp)	Subtracts arguments and returns the difference.
	adddate(date, interval \| int)	Returns the result of a given datetime plus the time interval of a specified unit.
	subdate(date, interval \| int)	Returns the result of a given datetime minus the time interval of a specified unit.
	addtime(timestamp \| time \| text, interval \| text)	Returns the result of a given datetime plus the time interval of a specified unit.
	subtime(timestamp \| time \| text, interval \| text)	Returns the result of a given datetime minus the time interval of a specified unit.
	date_add(date, interval)	Returns the result of a given datetime plus the time interval of a specified unit.
	date_sub(date, interval)	Returns the result of a given datetime minus the time interval of a specified unit.
	timestampadd(field, numeric, timestamp)	Adds an integer interval in the unit of field (the number of seconds can be a decimal) to a datetime expression.
	timestampdiff(field, timestamp1, timestamp2)	Subtracts timestamp1 from timestamp2 and returns the difference in the unit of field.
	timediff(timestamp \| time \| text, timestamp \| time \| text)	Subtracts a date from another date.
	datediff(date1, date2)	Returns the number of days between two given dates.
	period_add(P, N)	Returns the date of a given period plus N months.
	period_diff(P1, P2)	Returns the number of months between two given dates.
	add_months(d,n)	Adds a specified number of months to a date.
Time and date extraction functions	date_part(text, timestamp \| interval)	Extracts a specified part from a date or time expression.
	date_trunc(text, timestamp with time zone \| timestamp without time zone \| interval)	Truncates a time or date to the specified precision.
	trunc(timestamp)	Truncates a timestamp to day.
	extract(field from timestamp \| time \| interval)	Extracts a specified time part from a date or time expression.
Functions for obtaining the current time and date	clock_timestamp()	Returns the current timestamp of the real-time clock.
	current_date	Returns the current date.
	curdate()	Returns the current date. This function is compatible with MySQL.
	current_time	Returns the current time.
	curtime([fsp])	Returns the current time.
	current_timestamp	Returns the current date and time (start time of the current transaction).
	localtime	Returns the current time.
	localtimestamp	Returns the current date and time.
	statement_timestamp()	Returns the current date and time (start time of the current transaction).
	sysdate	Returns the current date and time of the system.
	timeofday()	Returns the current date and time.
	transaction_timestamp()	Returns the system date and time when the current transaction starts.
Time and date formatting functions	date_format(timestamp, fmt)	Converts a date into a string in the format specified by fmt.
	time_format(time, fmt)	Converts a date into a string in the format specified by fmt.
	str_to_date(str, format)	Converts a string of the date or time type to a value of the date type according to the provided display format.
	str_to_time(str, format)	Converts a string of the time type to a value of the time type according to the provided display format.
Other time and date processing functions	justify_days(interval)	Sets the unit of time interval to 30 days.
	justify_hours(interval)	Sets the unit of time interval to 24 hours.
	justify_interval(interval)	Adjusts the interval using justify_days and justify_hours.
	numtodsinterval(num, text)	Converts a numeric value into an INTERVAL DAY TO SECOND literal based on a specified unit.
	convert_tz(timestamp, from_tz, to_tz)	Description: Converts the datetime value from the time zone provided by from_tz (text) to the time zone provided by to_tz (text), and returns the converted datetime.
	isfinite(date)	Checks whether the date is finite.
	isfinite(timestamp)	Checks whether a timestamp is valid.
	isfinite(interval)	Checks whether an interval is valid.
	pg_sleep(seconds)	Returns the server thread delay.