Machine Learning
Date Time In SQL

Date Time In SQL

Jeevan Henry Dsouza's photo
Jeevan Henry Dsouza
·

4 min read

Table of contents

Introduction

  • In SQL, datetime variables are used to store date and time values. They allow you to work with temporal data, such as recording timestamps for events, scheduling tasks, or storing birthdates and other time-related information. The specific data types and formats for datetime variables can vary depending on the database management system (DBMS) you are using, but some common datetime data types include:

  1. DATE: Stores a date value in the format 'YYYY-MM-DD'. It represents a specific calendar date without any time information.

  2. TIME: Stores a time value in the format 'HH:MM:SS'. It represents a specific time of day without any date information.

  3. DATETIME / TIMESTAMP: Stores both date and time values in the format 'YYYY-MM-DD HH:MM:SS'. The DATETIME type is supported in some database systems like MySQL, while TIMESTAMP is used in others like PostgreSQL and SQL Server. These types are typically used to record events with both date and time information.

  4. DATETIME2: Similar to DATETIME but provides higher precision and a larger range of supported dates and times.

  5. TIMESTAMP WITH TIME ZONE: Stores date and time along with the time zone information.

  6. INTERVAL: Stores a duration or interval of time, such as '1 day' or '2 hours'.

The syntax for working with datetime variables varies between DBMS, but here are some common examples of SQL queries involving datetime variables:

  1. Inserting a datetime value into a table:
INSERT INTO events (event_name, event_datetime) VALUES ('Meeting', '2023-07-22 15:30:00');
  1. Querying records based on a specific date range:
SELECT * FROM events WHERE event_datetime BETWEEN '2023-07-22' AND '2023-07-23';
  1. Calculating time differences between two datetime values:
SELECT TIMESTAMPDIFF(MINUTE, start_time, end_time) AS duration_in_minutes FROM tasks;
  1. Formatting datetime values in the result:
SELECT event_name, DATE_FORMAT(event_datetime, '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM events;

Please note that the specific functions and syntax for working with datetime variables may differ between database systems. It's essential to refer to the documentation of the specific DBMS you are using for accurate and detailed information.

Extraction Functions

SQL provides various extraction functions that allow you to retrieve specific parts of date and time values. These functions are useful when you want to extract specific components, such as the year, month, day, hour, minute, or second, from datetime variables. The exact set of extraction functions may vary depending on the database management system (DBMS) you are using, but here are some common extraction functions in SQL:

  1. EXTRACT(): This function is used to extract a specific part of a datetime value. The syntax can vary between DBMS, but here's a general example:
-- Extract the year from a datetime value
SELECT EXTRACT(YEAR FROM my_datetime_column) AS year FROM my_table;
  1. YEAR(): This function extracts the year from a datetime value from a column . It is supported in many database systems.
-- Extract the year from a datetime value
SELECT YEAR(my_datetime_column) AS year FROM my_table;
  1. MONTH(): Extracts the month from a datetime value from a column.
-- Extract the month from a datetime value
SELECT MONTH(my_datetime_column) AS month FROM my_table;
  1. DAY(): Extracts the day of the month from a datetime value from a cloumn.
-- Extract the day of the month from a datetime value
SELECT DAY(my_datetime_column) AS day FROM my_table;
  1. HOUR(): Extracts the hour from a datetime value from a column.
-- Extract the hour from a datetime value
SELECT HOUR(my_datetime_column) AS hour FROM my_table;
  1. MINUTE(): Extracts the minute from a datetime value from a column.
-- Extract the minute from a datetime value
SELECT MINUTE(my_datetime_column) AS minute FROM my_table;
  1. SECOND(): Extracts the second from a datetime value.
-- Extract the second from a datetime value
SELECT SECOND(my_datetime_column) AS second FROM my_table;
  1. DAYOFWEEK() / WEEKDAY(): These functions return the day of the week (1 = Sunday, 2 = Monday, etc.) from a datetime value from a column.
-- Extract the day of the week from a datetime value
SELECT DAYOFWEEK(my_datetime_column) AS day_of_week FROM my_table;
datetimeSQL