# Datetime Sql ## Introduction 1. Timestamp * In SQL, a `TIMESTAMP` is a data type used to store date and time values. It represents a point in time, typically including both a date and a time component. The format of the timestamp can vary between database systems, but it often includes information about the year, month, day, hour, minute, second, and sometimes even fractions of a second. Here's a basic example of using the `TIMESTAMP` data type in SQL: ```sql CREATE TABLE your_table ( id INT PRIMARY KEY, event_name VARCHAR(255), event_timestamp TIMESTAMP ); INSERT INTO your_table (id, event_name, event_timestamp) VALUES (1, 'Event 1', '2023-11-24 12:30:00'), (2, 'Event 2', '2023-11-25 14:45:30'); ``` In this example: * `event_timestamp` is a column of type `TIMESTAMP` in the `your_table` table. * The `INSERT` statement adds two rows to the table, each with an event name and a timestamp. ## Some Important Functions 1. Extract() * In SQL, the `EXTRACT` function is used to extract components (such as year, month, day, hour, minute, etc.) from a date or timestamp. The exact usage and available components can vary between database systems, but the general syntax is as follows: ```sql EXTRACT(field FROM source) ``` Here, `field` specifies the component to be extracted, and `source` is the date or timestamp from which to extract the information. ### Example Usage: Let's assume you have a table named `your_table` with a `timestamp_column` of type `TIMESTAMP`. Here are some examples: #### Extract Year: ```sql SELECT EXTRACT(YEAR FROM timestamp_column) AS extracted_year FROM your_table; ``` #### Extract Month: ```sql SELECT EXTRACT(MONTH FROM timestamp_column) AS extracted_month FROM your_table; ``` #### Extract Day: ```sql SELECT EXTRACT(DAY FROM timestamp_column) AS extracted_day FROM your_table; ``` #### Extract Hour: ```sql SELECT EXTRACT(HOUR FROM timestamp_column) AS extracted_hour FROM your_table; ``` #### Extract Minute: ```sql SELECT EXTRACT(MINUTE FROM timestamp_column) AS extracted_minute FROM your_table; ``` #### Extract Second: ```sql SELECT EXTRACT(SECOND FROM timestamp_column) AS extracted_second FROM your_table; ``` The `EXTRACT` function is useful for various operations where you need to analyze or filter data based on specific components of date and time values. 2. Date\_Truncate * The `DATE_TRUNC` function is used to truncate or round a date or timestamp value to a specified precision. It is often used to remove the less significant parts of a date or time, effectively rounding it to a specific unit. The exact usage and available units can vary between different database systems. Here is the general syntax for `DATE_TRUNC`: ```sql DATE_TRUNC(unit, source) ``` * `unit`: Specifies the unit to which the date or timestamp should be truncated (e.g., 'year', 'month', 'day', 'hour', etc.). * `source`: The date or timestamp value that you want to truncate. ### Example Usage: Let's assume you have a table named `your_table` with a `timestamp_column` of type `TIMESTAMP`. Here are some examples: #### Truncate to Year: ```sql SELECT DATE_TRUNC('year', timestamp_column) AS truncated_year FROM your_table; ``` #### Truncate to Month: ```sql SELECT DATE_TRUNC('month', timestamp_column) AS truncated_month FROM your_table; ``` #### Truncate to Day: ```sql SELECT DATE_TRUNC('day', timestamp_column) AS truncated_day FROM your_table; ``` #### Truncate to Hour: ```sql SELECT DATE_TRUNC('hour', timestamp_column) AS truncated_hour FROM your_table; ``` #### Truncate to Minute: ```sql SELECT DATE_TRUNC('minute', timestamp_column) AS truncated_minute FROM your_table; ``` #### Truncate to Second: ```sql SELECT DATE_TRUNC('second', timestamp_column) AS truncated_second FROM your_table; ``` 3. Interval * In SQL, the `INTERVAL` keyword is used to define a time duration or interval. It can be used in various contexts, such as when adding or subtracting time from a date or timestamp or when specifying a time duration in queries. Here are some common use cases for the `INTERVAL` keyword: ### 1\. Adding an Interval to a Timestamp: ```sql -- Add an interval of 3 days to a timestamp SELECT CURRENT_TIMESTAMP + INTERVAL '3' DAY AS new_timestamp; ``` ### 2\. Subtracting an Interval from a Timestamp: ```sql -- Subtract an interval of 1 hour from a timestamp SELECT CURRENT_TIMESTAMP - INTERVAL '1' HOUR AS new_timestamp; ``` ### 3\. Using INTERVAL in WHERE Clause: ```sql -- Select records where the timestamp is within the last 7 days SELECT * FROM your_table WHERE timestamp_column > CURRENT_TIMESTAMP - INTERVAL '7' DAY; ``` ### 4\. Using INTERVAL in Date Arithmetic: ```sql -- Calculate the difference between two timestamps SELECT timestamp2 - timestamp1 AS time_difference FROM your_table; ``` ### 5\. Extracting Components from an Interval: ```sql -- Extract the number of days from an interval SELECT EXTRACT(DAY FROM INTERVAL '5' DAY) AS days; ``` In these examples, the `INTERVAL` keyword is used to specify a duration, and it is often followed by a numeric value and a unit (e.g., '3' DAY or '1' HOUR). The unit can be days, hours, minutes, seconds, and so on, depending on the requirement. 4. Finding actual time difference using Extract * Certainly! Here are some more examples of using intervals with timestamps in SQL: ### 1\. Calculate the Interval in Days: ```sql -- Calculate the interval in days between two timestamps SELECT timestamp2 - timestamp1 AS days_interval FROM your_table; ``` ### 2\. Calculate the Interval in Hours: ```sql -- Calculate the interval in hours between two timestamps SELECT EXTRACT(HOUR FROM timestamp2 - timestamp1) AS hours_interval FROM your_table; ``` ### 3\. Calculate the Interval in Minutes: ```sql -- Calculate the interval in minutes between two timestamps SELECT EXTRACT(MINUTE FROM timestamp2 - timestamp1) AS minutes_interval FROM your_table; ``` ### 4\. Calculate the Interval in Seconds: ```sql -- Calculate the interval in seconds between two timestamps SELECT EXTRACT(SECOND FROM timestamp2 - timestamp1) AS seconds_interval FROM your_table; ``` ### 5\. Add an Interval to a Timestamp: ```sql -- Add an interval of 3 days to a timestamp SELECT timestamp + INTERVAL '3' DAY AS new_timestamp FROM your_table; ``` ### 6\. Subtract an Interval from a Timestamp: ```sql -- Subtract an interval of 1 hour from a timestamp SELECT timestamp - INTERVAL '1' HOUR AS new_timestamp FROM your_table; ```