Date Extraction and Truncation in Bold BI using Expressions
In Bold BI, it is possible to extract date components using SQL expressions. Below are some common date extraction functions that work with popular databases like PostgreSQL, MySQL, and SQL Server.
Extracting Parts of a Date:
MySQL / PostgreSQL:
Sample Data:
Syntax 1:
EXTRACT(field FROM timestamp)
Explanation:
The EXTRACT function is frequently utilized to retrieve a particular component, such as year, month, or day, from a timestamp or datetime field. The field parameter indicates which component should be extracted from the timestamp.
For example, if you have a timestamp field containing the value ‘2024-04-08 12:34:56’, and you use EXTRACT (YEAR FROM timestamp), it will return ‘2024’. Similarly, if you use EXTRACT (MONTH FROM timestamp), it will return ‘4’.
So, the value of field determines what specific part of the timestamp you want to extract.
Example:
EXTRACT(DAY FROM [last_update])
Syntax 2:
DATE_FORMAT(date, format)
Explanation:
- date: This is the specific date or time value that you wish to format.
- format: This string specifies the preferred format for the date, utilizing format specifiers to denote various date and time components.
The format string can contain various format specifiers, such as: - %Y: Year (4 digits)
- %y: Year (2 digits)
- %m: Month (01 to 12)
- %d: Day of the month (01 to 31)
- %H: Hour (00 to 23) - 24-hour format
- %h: Hour (01 to 12) - 12-hour format
- %i: Minutes (00 to 59)
- %s: Seconds (00 to 59)
- %W: Day of the week (Sunday to Saturday)
- %M: Month name (January to December)
- %b: Abbreviated month name (Jan to Dec)
- %a: Abbreviated day name (Sun to Sat)
- %p: AM or PM
Example:
DATE_FORMAT([last_update], '%p')
PostgreSQL:
Syntax 1:
DATE_PART(unit, date)
Explanation:
- unit: This identifies the specific component of the date or time that you wish to retrieve, whether it be a unit such as year, month, day, hour, minute, second, etc.
- date: This is the date or timestamp value that you wish to extract the specified unit from.
Example:
DATE_PART('month', hire_date)
Syntax 2:
EXTRACT(field FROM timestamp)
Example:
EXTRACT(DAY FROM [last_update])
SQL Server:
Sample Data:
Syntax:
DATEPART(unit, date)
Explanation:
The DATEPART function is utilized for extracting a particular date or time component from a provided date or time value.
- unit: This indicates the specific component of the date or time that you wish to retrieve, which may include units such as year, month, day, hour, minute, or second.
- date: This is the specific date or time value that you wish to extract the specified unit from.
Example:
DATEPART(DAY, [OrderDate])
Truncating a Date:
MySQL:
Syntax:
DATE(date)
The DATE() function extracts the date from a given datetime expression by removing the time portion and keeping only the date. Other similar functions such as YEAR(), MONTH(), HOUR(), MINUTE()
Example:
DATE([last_update])
PostgreSQL:
Syntax:
DATE_TRUNC('unit', date)
Example:
DATE_TRUNC('month', [start_date])
SQL Server:
Syntax:
DATEADD(day, DATEDIFF(day, 0, [date_field]), 0)
The DATEADD and DATEDIFF functions are often used in conjunction to calculate dates.
Example:
DATEADD(month, DATEDIFF(month, 0, [OrderDate]), 0)
Explanation:
The SQL expression DATEADD(month, DATEDIFF(month, 0, ‘7/4/1996 12:00:00 AM’), 0) calculates the difference in months between the specified datetime value (‘7/4/1996 12:00:00 AM’) and the base datetime value ‘0’, which signifies the inception of the SQL Server datetime scale (January 1, 1900). This variance, measured in months, is then added to ‘0’, effectively resetting the day and time components to the start of the month while preserving the year and month details. Consequently, the outcome is ‘7/1/1996 00:00:00.000’, with the day and time elements set to the beginning of July 1996.
Note
If you want to change the date format to a different format like day, month, year, or time without creating a new expression, you can easily do so by dragging and dropping the data, switching to assign data, and modifying the format as needed.
Additional References
