Be able to set display format of date/time data

Cambridge IGCSE Information Communication Technology (ICT) 0417 – Topic 18: Databases

Objective

Be able to set the display format of date/time data in relational database management systems.

Key Concepts

• Date and Time Data Types – DATE, TIME, DATETIME, TIMESTAMP, INTER \cdot AL.
• Formatting Functions – Functions that convert a date/time value into a string with a specified format.
• Locale and Time Zone Considerations – How databases handle time zones and regional formatting.

Formatting Functions by Database System

Common Format Specifiers

• %d – Day of month (01–31) – MySQL
• DD – Day of month (01–31) – PostgreSQL/Oracle
• %m – Month (01–12) – MySQL
• MM – Month (01–12) – PostgreSQL/Oracle
• %Y – Year (four digits) – MySQL
• YYYY – Year (four digits) – PostgreSQL/Oracle
• HH – Hour (01–12) – PostgreSQL/Oracle
• hh – Hour (01–12) – MySQL
• HH24 – Hour (00–23) – PostgreSQL/Oracle
• hh24 – Hour (00–23) – MySQL
• MI – Minutes – PostgreSQL/Oracle
• %i – Minutes – MySQL
• SS – Seconds – PostgreSQL/Oracle
• %s – Seconds – MySQL

Setting the Default Display Format

1. Determine the database system in use.
2. Identify the column that stores the date/time value.
3. Use the appropriate formatting function in a SELECT statement to present the data.
4. For reports or application interfaces, embed the formatting function in the query or in the presentation layer.

Example: Displaying Order Dates in a Report

Suppose you have a table orders with a column order_date of type DATETIME. You want the report to show dates as DD/MM/YYYY in MySQL:

SELECT order_id,
       DATE_FORMAT(order_date, '%d/%m/%Y') AS formatted_date
FROM orders
ORDER BY order_date DESC;

Time Zone Conversion

When working with TIMESTAMP data that stores UTC, you may need to convert it to the local time zone before formatting:

• MySQL: CON \cdot ERT_TZ(timestamp_col, 'UTC', 'America/New_York')
• PostgreSQL: timestamp_col AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'
• Oracle: FROM_TZ(CAST(timestamp_col AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York'
• SQL Server: AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'

Practice Questions

1. Write a MySQL query that returns the current date in the format YYYY-MM-DD.
2. In PostgreSQL, how would you display a TIMESTAMP column as Month DD, YYYY HH24:MI?
3. Explain the difference between DATE_FORMAT and TO_CHAR in terms of syntax and supported specifiers.
4. Show how to convert a UTC TIMESTAMP to British Summer Time (BST) and format it as DD-MMM-YYYY HH24:MI in Oracle.

Key Takeaways

• Use the database‑specific formatting function to control how dates and times appear.
• Remember that format specifiers differ between systems; always refer to the documentation.
• When time zones are involved, convert before formatting to avoid incorrect displays.
• Consistent formatting improves readability and ensures that reports meet user expectations.