DEV Community

Michael
Michael

Posted on

GBase 8a Date Functions: A Complete Guide to Day-Related Built-ins

#gbase #database #数据库

Date manipulation is one of the most frequent tasks in SQL development. GBase 8a, the China-domestically developed MPP cluster database from GBASE, ships with a powerful set of built-in functions designed to handle day-level computations efficiently and at scale.

This guide breaks down six core day-related functions in GBase 8a. Whether you're aggregating weekly reports, converting day-of-year values, or calculating ancient-day offsets for a gbase database, you'll find practical patterns and ready-to-use SQL snippets below.

Day-of-Week Functions: DAYOFWEEK and WEEKDAY

DAYOFWEEK

  • Syntax: DAYOFWEEK(date)
  • Returns: An integer representing the day of the week for a given date. 1 = Sunday, 2 = Monday, ..., 7 = Saturday.
  • Use case: Scenarios that treat Sunday as the first day of the week.

WEEKDAY

  • Syntax: WEEKDAY(date)
  • Returns: An integer representing the day of the week. 0 = Monday, 1 = Tuesday, ..., 6 = Sunday.
  • Use case: Work-week summaries where Monday is the logical start.

Choose WEEKDAY when your business calendar aligns with ISO/Chinese weekly conventions; use DAYOFWEEK if your reporting standard starts the week on Sunday.

Day-of-Year Pair: DAYOFYEAR and MAKEDATE

These two are inverse functions. DAYOFYEAR tells you which day of the year a date falls on; MAKEDATE reconstructs a date from a year and a day number. They are especially useful for year-over-year comparisons and cumulative daily reports in a gbase database environment.

DAYOFYEAR

  • Syntax: DAYOFYEAR(date)
  • Returns: The day of the year, from 1 to 366. 
gbase> SELECT dayofyear('2021-01-01');

+---+
| dayofyear('2021-01-01') |
+---+
|                       1 |
+---+

gbase> SELECT dayofyear('2021-12-31');

+---+
| dayofyear('2021-12-31') |
+---+
|                     365 |
+---+
Enter fullscreen mode Exit fullscreen mode

MAKEDATE

  • Syntax: MAKEDATE(year, dayofyear)
  • Returns: A date value constructed from the given year and day-of-year. The dayofyear argument must be greater than 0; otherwise, the function returns NULL. 
gbase> SELECT makedate(2021, 32);
+-------------------+
| makedate(2021,32) |
+-------------------+
| 2021-02-01        |
+-------------------+

gbase> SELECT makedate(2021, 365);
+--------------------+
| makedate(2021,365) |
+--------------------+
| 2021-12-31         |
+--------------------+
Enter fullscreen mode Exit fullscreen mode

Epoch-Day Functions: TO_DAYS and FROM_DAYS

When you need to compute large day-offsets from year zero—say, for historical data archiving or long-range interval calculations—TO_DAYS and FROM_DAYS are your go-to tools.

TO_DAYS

  • Syntax: TO_DAYS(date)
  • Returns: The total number of days since year 0.
  • Important: This function is not suitable for dates before 1582 (the adoption of the Gregorian calendar), because the days lost during the calendar reform are not accounted for. 
gbase> SELECT to_days('2020-01-01');
+-----------------------+
| to_days('2020-01-01') |
+-----------------------+
|                737790 |
+-----------------------+
Enter fullscreen mode Exit fullscreen mode

FROM_DAYS

  • Syntax: FROM_DAYS(N)
  • Returns: The DATE value corresponding to N days since year 0. This is the inverse of TO_DAYS.
  • Edge case: The minimum valid input is 366. An argument of 365 or less yields NULL. 
gbase> SELECT from_days(738156);
+-------------------+
| from_days(738156) |
+-------------------+
| 2021-01-01        |
+-------------------+

gbase> SELECT from_days(366);
+----------------+
| from_days(366) |
+----------------+
| 0001-01-01     |
+----------------+

gbase> SELECT from_days(365);
+----------------+
| from_days(365) |
+----------------+
| NULL           |
+----------------+
Enter fullscreen mode Exit fullscreen mode

Why These Functions Matter

When working with a massively parallel database such as GBase 8a, pushing date logic into native SQL functions rather than handling it in application code allows the MPP engine to distribute and parallelize the computation efficiently. Whether you’re building a weekly sales dashboard or archiving decade-spanning records, these six functions give you a clean, performant way to manipulate dates in a gbase database.

GBASE continues to optimize its GBase 8a MPP Cluster for analytical workloads—these built-in day functions are small but powerful tools that can make a measurable difference in query performance.

Looking ahead, as GBase 8a’s adoption grows across finance, telecom, and government sectors, mastering these fundamentals will help teams write cleaner queries and get the most out of their China-domestically developed data platforms.

Top comments (0)