Help Center/ Data Warehouse Service/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Time and Date Functions and Operators/ Time and Date Functions/ Basic Time and Date Functions
Updated on 2026-03-04 GMT+08:00
View PDF
Share

Basic Time and Date Functions

day(date | timestamp with time zone)

Description: Returns the day (that is, the day in a month) of a given date or time. This function is the same as the dayofmonth(date) function.

Value range: 1 to 31

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT day('2025-06-28');
 day
-----
  28
(1 row)
SELECT day('2025-06-28 09:00:00+08');
 day
-----
  28
(1 row)

weekday(date | timestamp with time zone)

Description: Returns the week of a given date or time. Monday is the start day of a week.

Value range: 0 to 6

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT weekday('2020-11-06');
 weekday
---------
       4
(1 row)
SELECT weekday('2020-11-06 09:00:00+08');
 weekday
---------
       4
(1 row)

week(date[, mode])

Description: Returns the week number in the year of the given date or time based on the mode. The default value of mode is 0.

  • date: specifies the date for which the week number is to be obtained.
  • mode (optional): controls the start of a week and the first week of a year. By default, the first day of a week is Sunday. You can also specify whether a week starts from Monday or Sunday. For details about mode, see the following table.

Return type: integer

Table 1 Working principle of the mode in the week function

Mode

First Day of a Week

Week Range

Rule for Determining the First Week

0

Sunday

0-53

The first week starts from the first Sunday of the year. The previous week is week 0.

1

Monday

0-53

If the number of days from January 1 to the first Monday is greater than 3, the week is the first week of the year. Otherwise, the week is week 0.

2

Sunday

1-53

The first week starts from the first Sunday of the year. The previous week is week 5x of the previous year.

3

Monday

1-53

If the number of days from January 1 to the first Monday is greater than 3, the week is the first week of the year. Otherwise, it is week 5x of the previous year.

4

Sunday

0-53

If the number of days from January 1 to the first Sunday is greater than 3, the week is the first week of the year. Otherwise, the week is week 0.

5

Monday

0-53

The first week starts from the first Monday of the year. The previous week is week 0.

6

Sunday

1-53

If the number of days from January 1 to the first Sunday is greater than 3, the week is the first week of the year. Otherwise, it is week 5x of the previous year.

7

Monday

1-53

The first week starts from the first Monday of the year. The previous week is week 5x of the previous year.

Examples:

Obtain the week number of a specified date (use the default value 0 of mode, that is, Sunday is the first day of a week).

1
2
3
4
5
 
SELECT WEEK('2026-01-01');
 week
------
0 -- January 1 is not in the first week of 2026. According to the rule of mode 0, January 1 is in the last week of the previous year. Therefore, 0 is returned.
(1 row)

Obtain the week number of a specified date (set mode to 1, that is, Monday is the first day of a week according to the ISO standard).

1
2
3
4
5
 
SELECT WEEK('2026-01-01', 1);
 week
------
    1 -- According to the rule of mode 1, January 1, 2026 is in the first week of the year.
(1 row)

month(date | timestamp with time zone)

Description: Returns the month of a given date or time.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT month('2025-11-30');
 month
-------
    11
(1 row)
SELECT month('2025-11-30 09:00:00+08');
 month
-------
    11
(1 row)

quarter(date | timestamp with time zone)

Description: Returns the quarter to which the date or time belongs. The quarter ranges from 1 to 4.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT quarter(date '2025-12-13');
 quarter
---------
       4
(1 row)
SELECT quarter('2025-03-20 09:00:00+08');
 quarter
---------
       1
(1 row)

year(date | timestamp with time zone)

Description: Returns the year of a given date or time.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT year('2025-11-13');
 year
------
 2025
(1 row)
SELECT year('2025-11-13 09:00:00+08');
 year
------
 2025
(1 row)

yearweek(date | timestamp with time zone[, mode])

Description: Returns the year and the number of weeks in the year corresponding to a given date or time. The number of weeks ranges from 1 to 53.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT yearweek('2025-11-13');
 yearweek
----------
   202545
(1 row)
SELECT yearweek('2025-1-1');
 yearweek
----------
   202452
(1 row)

dayofweek(date | timestamp with time zone)

Description: Returns the week of a given date or time. Sunday is the start day of a week.

Value range: 1 to 7

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT dayofweek('2025-11-22');
 dayofweek
-----------
         7
(1 row)
SELECT dayofweek('2025-11-22 09:00:00+08');
 dayofweek
-----------
         7
(1 row)

dayofmonth(date | timestamp with time zone)

Description: Returns the number of days in the month of a given date or time.

Value range: 1 to 31

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT dayofmonth('2025-10-28');
 dayofmonth
------------
         28
(1 row)
SELECT dayofmonth('2025-10-28 09:00:00+08');
 dayofmonth
------------
         28
(1 row)

dayofyear(date | timestamp with time zone)

Description: Returns the day of a given date or time in the year. January 1 is the first day, and December 31 is 365 or 366 days.

Value range: 1 to 366

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT dayofyear('2025-10-28');
 dayofyear
-----------
       301
(1 row)
SELECT dayofyear('2025-10-28 09:00:00+08');
 dayofyear
-----------
       301
(1 row

weekofyear(date | timestamp with time zone)

Description: Returns the week number of a given date or time in the year. It is equivalent to week(date, 3).

Value range: 1 to 53

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT weekofyear('2025-11-30');
 weekofyear
------------
         48
(1 row)
SELECT weekofyear('2025-11-30 09:00:00+08');
 weekofyear
------------
         48
(1 row)

makedate(year, dayofyear)

Description: Creates a date based on the given year and day of the year.

Return type: date

Example:

1
2
3
4
5
 
SELECT makedate(2025, 60);
  makedate
------------
 2025-03-01
(1 row)

maketime(hour, minute, second)

Description: Generates a time value based on the given hour, minute, and second.

The value of the time type in DWS ranges from 00:00:00 to 24:00:00. Therefore, this function is not available when the value of hour is greater than 24 or less than 0.

Return type: time

Example:

1
2
3
4
5
 
SELECT maketime(12, 15, 30.12);
  maketime
-------------
 12:15:30.12
(1 row)

hour(timestamp with time zone)

Description: Returns the hour value in a timestamp.

Return type: integer

Example:

1
2
3
4
5
 
SELECT hour(timestamptz '2025-12-13 12:11:15+06');
 hour
------
   14
(1 row)

minute(timestamp with time zone)

Description: Returns the minute value in the time.

Return type: integer

Example:

1
2
3
4
5
 
SELECT minute(timestamptz '2025-12-13 12:11:15+06');
 minute
--------
     11
(1 row)

second(timestamp with time zone)

Description: Returns the second value in a timestamp.

Return type: integer

Example:

1
2
3
4
5
 
SELECT second(timestamptz '2025-12-13 12:11:15+06');
 second
--------
     15
(1 row)

microsecond(timestamp with time zone)

Description: Returns the microsecond value in the time.

Return type: integer

Example:

1
2
3
4
5
 
SELECT microsecond(timestamptz '2025-12-13 12:11:15.123634+06');
 microsecond
-------------
      123634
(1 row)

from_unixtime(unix_timestamp[,format])

Description: Converts a Unix timestamp to the datetime type when the format string is set to the default value. If the format string is specified, this function converts the Unix timestamp to a string of a specified format.

Return type: timestamp (default format string) or text (specified format string)

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT from_unixtime(875996580);
    from_unixtime
---------------------
 1997-10-04 20:23:00
(1 row)
SELECT from_unixtime(875996580, '%Y %D %M %h:%i:%s');
       from_unixtime
---------------------------
 1997 5th October 04:23:00
(1 row)

unix_timestamp([timestamp with time zone])

Description: 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). If there is no input parameter, the current time is used.

Return type: bigint (no parameter is input) or numeric (parameter is input)

Example:

1
2
3
4
5
 
SELECT unix_timestamp();
 unix_timestamp
----------------
     1768793418
(1 row)

1
2
3
4
5
 
SELECT unix_timestamp('2025-09-08 12:11:13+06');
    unix_timestamp
-------------------
 1757311873.000000
(1 row)

now([fsp])

Description: Returns the start date and time of the current transaction. The input parameter determines the microsecond precision. The default value is 6.

Return type: timestamp with time zone

Example:

1
2
3
4
5
 
SELECT now();
              now              
-------------------------------
 2026-01-19 11:35:09.082256+08
(1 row)

1
2
3
4
5
 
SELECT now(3);
             now    
---------------------------- 
 2026-01-19 11:35:31.839+08
(1 row)

last_day(d)

Description: Returns the date or time of the last day of the month that contains date.

Return type:

  • In the ORA- or TD-compatible mode, a timestamp is returned.
  • In the MySQL-compatible mode, a date is returned.

Example:

1
2
3
4
5
 
SELECT last_day(to_date('2026-01-01', 'YYYY-MM-DD')) AS cal_result;
     cal_result      
---------------------
 2026-01-31 00:00:00
(1 row)

next_day(x,y)

Description: 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.

Return type:

  • In the ORA- or TD-compatible mode, a timestamp is returned.
  • In the MySQL-compatible mode, a date is returned.

Example:

1
2
3
4
5
 
SELECT next_day(timestamp '2025-05-21 00:00:00','Sunday')AS cal_result;
     cal_result      
---------------------
 2025-05-25 00:00:00
(1 row)

from_days(days)

Description: Returns the date value based on the given number of days.

Return type: date

Example:

1
2
3
4
5
 
SELECT from_days(730669);
 from_days
------------
 2000-07-03
(1 row)

to_days(timestamp)

Description: Returns the number of days since the year 0 based on the specified date.

Return type: integer

Example:

1
2
3
4
5
 
SELECT to_days(timestamp '2025-10-07');
 to_days
---------
  739896
(1 row)
Parent Topic: Time and Date Functions