MCQ King

SQLite – 21 – Date and Time Functions

Start MCQ

SQLite provides a set of powerful date and time functions that allow you to work with date and time values within your SQL queries. These functions can help you manipulate, format, and extract information from date and time data stored in your SQLite database. In this discussion, we’ll explore some of the most commonly used date and time functions in SQLite, along with practical examples.

SQLite’s date and time functions can be broadly categorized into the following groups:

  1. Current Date and Time Functions: Functions that provide the current date and time.
  2. Date and Time Manipulation Functions: Functions that allow you to manipulate and modify date and time values.
  3. Date and Time Formatting Functions: Functions that help you format date and time values into a more readable format.
  4. Date and Time Extraction Functions: Functions that extract specific components (e.g., year, month, day) from date and time values.

Current Date and Time Functions:

  1. CURRENT_DATE: Returns the current date in ‘YYYY-MM-DD’ format. SELECT CURRENT_DATE; -- Example result: '2023-10-06'
  2. CURRENT_TIME: Returns the current time in ‘HH:MM:SS’ format. SELECT CURRENT_TIME; -- Example result: '15:30:45'
  3. CURRENT_TIMESTAMP: Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format. SELECT CURRENT_TIMESTAMP; -- Example result: '2023-10-06 15:30:45'

Date and Time Manipulation Functions:

  1. DATE: Extracts the date part from a datetime string. SELECT DATE('2023-10-06 15:30:45'); -- Example result: '2023-10-06'
  2. TIME: Extracts the time part from a datetime string. SELECT TIME('2023-10-06 15:30:45'); -- Example result: '15:30:45'
  3. DATETIME: Combines separate date and time strings into a single datetime string. SELECT DATETIME('2023-10-06', '15:30:45'); -- Example result: '2023-10-06 15:30:45'
  4. JULIANDAY: Converts a date or datetime string into a Julian day number, which is a continuous count of days since noon on November 24, 4714 BCE. SELECT JULIANDAY('2023-10-06'); -- Example result: 2464901.5

Date and Time Formatting Functions:

  1. STRFTIME: Formats a datetime value according to a specified format string. You can use various format codes to represent different components of the date and time. SELECT STRFTIME('%Y-%m-%d %H:%M:%S', '2023-10-06 15:30:45'); -- Example result: '2023-10-06 15:30:45'
  2. DATE: Returns the date part of a datetime value in ‘YYYY-MM-DD’ format. SELECT DATE('2023-10-06 15:30:45'); -- Example result: '2023-10-06'

Date and Time Extraction Functions:

  1. YEAR: Extracts the year from a date or datetime value. SELECT YEAR('2023-10-06'); -- Example result: 2023
  2. MONTH: Extracts the month from a date or datetime value. SELECT MONTH('2023-10-06'); -- Example result: 10
  3. DAY: Extracts the day of the month from a date or datetime value. SELECT DAY('2023-10-06'); -- Example result: 6
  4. HOUR: Extracts the hour from a time or datetime value. SELECT HOUR('15:30:45'); -- Example result: 15
  5. MINUTE: Extracts the minute from a time or datetime value. SELECT MINUTE('15:30:45'); -- Example result: 30
  6. SECOND: Extracts the second from a time or datetime value. SELECT SECOND('15:30:45'); -- Example result: 45

Practical Examples:

Let’s consider a practical example where you have a table named Events that stores events with their event date and time. You want to retrieve events that occurred in the year 2023 and format the date and time for display:

-- Retrieve events in the year 2023 SELECT EventName, STRFTIME('%Y-%m-%d %H:%M:%S', EventDateTime) AS FormattedDateTime FROM Events WHERE YEAR(EventDateTime) = 2023;

In this query:

  • We use the YEAR function to filter events that occurred in the year 2023.
  • We use STRFTIME to format the EventDateTime column into a more readable format.

Conclusion:

SQLite’s date and time functions are valuable tools for working with date and time values in your database. Whether you need to extract specific components, format dates and times for display, or perform date calculations, these functions provide the necessary functionality. Understanding how to use these functions can enhance your ability to work with date and time data effectively in SQLite databases.