Date and Time manipulation in Oracle SQL

In Oracle SQL, date and time manipulation is essential for many database operations, ranging from basic date arithmetic to complex formatting and extraction. Here’s a guide covering various common operations you might need.

1. Basic Date Arithmetic

Adding/Subtracting Days:

-- Add 5 days to a date
SELECT SYSDATE + 5 FROM dual;

-- Subtract 10 days from a date
SELECT SYSDATE - 10 FROM dual;

Adding/Subtracting Months:

-- Add 3 months to a date
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

-- Subtract 2 months from a date
SELECT ADD_MONTHS(SYSDATE, -2) FROM dual;

Adding/Subtracting Years:

-- Add 1 year to a date
SELECT ADD_MONTHS(SYSDATE, 12) FROM dual;

-- Subtract 1 year from a date
SELECT ADD_MONTHS(SYSDATE, -12) FROM dual;

2. Extracting Components from Date

Extract Year, Month, Day, Hour, Minute, Second:

SELECT 
    EXTRACT(YEAR FROM SYSDATE) AS year,
    EXTRACT(MONTH FROM SYSDATE) AS month,
    EXTRACT(DAY FROM SYSDATE) AS day,
    EXTRACT(HOUR FROM SYSTIMESTAMP) AS hour,
    EXTRACT(MINUTE FROM SYSTIMESTAMP) AS minute,
    EXTRACT(SECOND FROM SYSTIMESTAMP) AS second
FROM dual;

3. Formatting Dates

To Convert Date to String in a Specific Format:

-- Convert date to 'YYYY-MM-DD' format
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

-- Convert date to 'DD-Mon-YYYY HH24:MI:SS' format
SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS') FROM dual;

To Convert String to Date:

-- Convert string to date
SELECT TO_DATE('2024-06-02', 'YYYY-MM-DD') FROM dual;

-- Convert string with time to date
SELECT TO_DATE('02-Jun-2024 14:30:00', 'DD-Mon-YYYY HH24:MI:SS') FROM dual;

4. Finding Difference Between Dates

Difference in Days:

SELECT 
    TRUNC(TO_DATE('2024-12-31', 'YYYY-MM-DD') - TO_DATE('2024-01-01', 'YYYY-MM-DD')) AS difference_in_days 
FROM dual;

Difference in Hours, Minutes, Seconds:

-- Difference in hours
SELECT 
    (SYSDATE - (SYSDATE - 1)) * 24 AS difference_in_hours 
FROM dual;

-- Difference in minutes
SELECT 
    (SYSDATE - (SYSDATE - 1)) * 24 * 60 AS difference_in_minutes 
FROM dual;

-- Difference in seconds
SELECT 
    (SYSDATE - (SYSDATE - 1)) * 24 * 60 * 60 AS difference_in_seconds 
FROM dual;

5. Working with Time Zones

Convert between Time Zones:

-- Convert from current time zone to UTC
SELECT 
    FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'YOUR_TIME_ZONE') AT TIME ZONE 'UTC' 
FROM dual;

-- Convert from one time zone to another
SELECT 
    FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'Asia/Kolkata' 
FROM dual;

6. Miscellaneous Functions

Last Day of the Month:

SELECT LAST_DAY(SYSDATE) FROM dual;

First Day of the Month:

SELECT TRUNC(SYSDATE, 'MM') FROM dual;

Next Day (Find next specific weekday):

-- Find the next Monday
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;

Truncate Date (to start of day, month, year, etc.):

-- Truncate to the start of the current day
SELECT TRUNC(SYSDATE) FROM dual;

-- Truncate to the start of the current month
SELECT TRUNC(SYSDATE, 'MM') FROM dual;

-- Truncate to the start of the current year
SELECT TRUNC(SYSDATE, 'YYYY') FROM dual;

These are some fundamental operations for date and time manipulation in Oracle SQL. Depending on your specific needs, you can combine these functions to perform more complex operations.

Oracle PL/SQL Date Manipulation Cheatcode:-

Date and Time manipulation in Apache Hive QL

In Apache Hive, date and time manipulation is crucial for data analysis and ETL processes. Hive provides a rich set of functions to perform various date and time operations. Here is a guide covering common operations you might need.

1. Basic Date Arithmetic

Adding/Subtracting Days:

-- Add 5 days to the current date
SELECT DATE_ADD(CURRENT_DATE, 5);

-- Subtract 10 days from the current date
SELECT DATE_SUB(CURRENT_DATE, 10);

Adding/Subtracting Months:

- Add 3 months to the current date
SELECT ADD_MONTHS(CURRENT_DATE, 3);

-- Subtract 2 months from the current date
SELECT ADD_MONTHS(CURRENT_DATE, -2);

2. Extracting Components from Date

Extract Year, Month, Day, Hour, Minute, Second:

SELECT 
    YEAR(CURRENT_DATE) AS year,
    MONTH(CURRENT_DATE) AS month,
    DAY(CURRENT_DATE) AS day,
    HOUR(CURRENT_TIMESTAMP) AS hour,
    MINUTE(CURRENT_TIMESTAMP) AS minute,
    SECOND(CURRENT_TIMESTAMP) AS second;

3. Formatting Dates

To Convert Date to String in a Specific Format:

-- Convert date to 'YYYY-MM-DD' format
SELECT DATE_FORMAT(CURRENT_DATE, 'yyyy-MM-dd');

-- Convert timestamp to 'DD-Mon-YYYY HH24:MI:SS' format
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, 'dd-MMM-yyyy HH:mm:ss');

To Convert String to Date:

-- Convert string to date
SELECT TO_DATE('2024-06-02', 'yyyy-MM-dd');

-- Convert string with time to timestamp
SELECT TO_TIMESTAMP('02-Jun-2024 14:30:00', 'dd-MMM-yyyy HH:mm:ss');

4. Finding Difference Between Dates

Difference in Days:

SELECT 
    DATEDIFF(TO_DATE('2024-12-31', 'yyyy-MM-dd'), TO_DATE('2024-01-01', 'yyyy-MM-dd')) AS difference_in_days;

Difference in Hours, Minutes, Seconds: Hive does not have built-in functions for calculating differences in hours, minutes, or seconds directly. You may need to use UNIX_TIMESTAMP and other functions for such calculations:

-- Difference in hours
SELECT 
    (UNIX_TIMESTAMP('2024-12-31 12:00:00') - UNIX_TIMESTAMP('2024-12-31 00:00:00')) / 3600 AS difference_in_hours;

-- Difference in minutes
SELECT 
    (UNIX_TIMESTAMP('2024-12-31 12:00:00') - UNIX_TIMESTAMP('2024-12-31 00:00:00')) / 60 AS difference_in_minutes;

-- Difference in seconds
SELECT 
    UNIX_TIMESTAMP('2024-12-31 12:00:00') - UNIX_TIMESTAMP('2024-12-31 00:00:00') AS difference_in_seconds;

5. Working with Time Zones

Hive does not directly support time zone conversion functions. This typically needs to be handled either during data ingestion or by using custom functions/UDFs.

6. Miscellaneous Functions

Last Day of the Month:

SELECT LAST_DAY('2024-06-02') AS last_day_of_month;

First Day of the Month:

SELECT DATE_TRUNC('MM', '2024-06-02') AS first_day_of_month;

Next Day (Find next specific weekday):

Truncate Date (to start of day, month, year, etc.):

-- Truncate to the start of the current day
SELECT DATE_TRUNC('DD', CURRENT_TIMESTAMP) AS start_of_day;

-- Truncate to the start of the current month
SELECT DATE_TRUNC('MM', CURRENT_TIMESTAMP) AS start_of_month;

-- Truncate to the start of the current year
SELECT DATE_TRUNC('YY', CURRENT_TIMESTAMP) AS start_of_year;

These operations should help you with most date and time manipulations in Hive. Depending on your specific requirements, you may need to combine these functions to achieve more complex transformations.

Hive treats dates as strings by default. Ensure consistent formatting (e.g., yyyy-MM-dd) when working with dates for accurate comparisons and manipulations. Consider using functions like from_unixtime or to_date for conversions if needed.

These are some core functions for manipulating dates and times in Hive. By combining them, you can achieve various tasks like finding dates N days before/after, calculating the difference between dates, etc., within your HiveQL queries.

Examples:–

what will be replacement of oracle last_day(add_months(sysdate,-1)) in Hive QL?

To achieve the equivalent of the Oracle function last_day(add_months(sysdate, -1)) in HiveQL, you need to:

1. Subtract one month from the current date: Use add_months(current_date, -1).
2. Get the last day of that month: Use the last_day function.

Here is the equivalent HiveQL query:

SELECT last_day(add_months(current_date, -1));

This query does the following:

• add_months(current_date, -1): Subtracts one month from the current date.
• last_day(...): Gets the last day of the month for the resulting date.

How to translate sas code put(datepart(olddate), MONYY7.) to pyspark codes . here olddate is a datetime value?

Hive QL Date Manipulation Cheatcode

1.How to remove datepart from Oracle dates which are is string format or string literals such as 2024-05-01 00:00:00?

To remove the time part from Oracle dates that are in string format or string literals, such as 2024-05-01 00:00:00, you can use various string manipulation functions available in SQL. Here are several methods to achieve this:

Using TO_DATE and TO_CHAR Functions

1. Convert String to Date and Format: If the date is in a string format, you can first convert it to a date and then format it back to a string without the time part.

SELECT TO_CHAR(TO_DATE('2024-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD') AS date_without_time
FROM dual;

Using SUBSTR Function

2. Extract Only the Date Part: Use the SUBSTR function to extract only the date part from the string.

SELECT SUBSTR('2024-05-01 00:00:00', 1, 10) AS date_without_time
FROM dual;

Using TRUNC Function for Date Type

3. Directly Truncate Date Type (if applicable): If you have an actual date column and you want to remove the time part, you can use the TRUNC function. However, this is for columns that are already in the DATE type.

SELECT TRUNC(TO_DATE('2024-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS date_without_time
FROM dual;

Example Usage in a Table

Assuming you have a table my_table with a column date_column that stores dates as strings in the format YYYY-MM-DD HH24:MI:SS, here are the SQL queries you could use:

Using TO_DATE and TO_CHAR:

SELECT TO_CHAR(TO_DATE(date_column, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD') AS date_without_time
FROM my_table;

Using SUBSTR:

SELECT SUBSTR(date_column, 1, 10) AS date_without_time
FROM my_table;

Using TRUNC (for actual DATE type columns):

If date_column is of DATE type, you can use TRUNC directly:

SELECT TRUNC(date_column) AS date_without_time
FROM my_table;

Full Example with a Data Table

Let’s create a full example with a sample table to illustrate these methods:

1. Create the Table:

CREATE TABLE my_table (
    id NUMBER,
    date_column VARCHAR2(19)
);

INSERT INTO my_table (id, date_column) VALUES (1, '2024-05-01 00:00:00');
INSERT INTO my_table (id, date_column) VALUES (2, '2024-06-15 12:34:56');

2. Query Using Different Methods:

• Using TO_DATE and TO_CHAR:

SELECT id, TO_CHAR(TO_DATE(date_column, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD') AS date_without_time
FROM my_table;

• Using SUBSTR:

SELECT id, SUBSTR(date_column, 1, 10) AS date_without_time
FROM my_table;

Output

The output for both queries would be:

ID  | DATE_WITHOUT_TIME
-----------------------
1   | 2024-05-01
2   | 2024-06-15

2.How to translate sas code put(datepart(olddate), MONYY7.) to pyspark sql. here olddate is a datetime value?

To translate the SAS code put(datepart(olddate), MONYY7.) to PySpark SQL, where olddate is a datetime value, you can use the combination of PySpark SQL functions to extract the date part and format it similarly to MONYY7. in SAS.

Here is how you can achieve this:

SAS Code Explanation:-

datepart(olddate): Extracts the date part from the datetime value.
put(…, MONYY7.): Formats the extracted date in MONYY7. format (e.g., JAN2024).
Equivalent in PySpark SQL
Extract the Date Part: Use the to_date function.
Format the Date Part: Use the date_format function to format the date.
Here’s a step-by-step breakdown:

Create a DataFrame with a datetime value:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, date_format

# Initialize SparkSession
spark = SparkSession.builder 
    .appName("DatePartExample") 
    .getOrCreate()

# Sample data
data = [("2024-05-01 15:30:00",)]

# Create DataFrame
df = spark.createDataFrame(data, ["olddate"])

df.show(truncate=False)
Extract the Date Part and Format:

python
Copy code
# Extract the date part and format it as 'MONYY'
df_formatted = df.withColumn("formatted_date", date_format(to_date(col("olddate")), "MMMYYYY"))

df_formatted.show(truncate=False)
Putting It All Together
Here's the full PySpark code that accomplishes the translation from SAS to PySpark SQL:

python
Copy code
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, date_format

# Initialize SparkSession
spark = SparkSession.builder 
    .appName("DatePartExample") 
    .getOrCreate()

# Sample data
data = [("2024-05-01 15:30:00",), ("2024-07-21 09:15:00",)]

# Create DataFrame
df = spark.createDataFrame(data, ["olddate"])

# Show initial DataFrame
df.show(truncate=False)

# Extract the date part and format it as 'MONYY'
df_formatted = df.withColumn("formatted_date", date_format(to_date(col("olddate")), "MMMYYYY"))

# Show the formatted DataFrame
df_formatted.show(truncate=False)
Expected Output

+-------------------+
|olddate            |
+-------------------+
|2024-05-01 15:30:00|
|2024-07-21 09:15:00|
+-------------------+

+-------------------+--------------+
|olddate            |formatted_date|
+-------------------+--------------+
|2024-05-01 15:30:00|May2024       |
|2024-07-21 09:15:00|Jul2024       |
+-------------------+--------------+

In this example, to_date extracts the date part from the datetime string, and date_format formats it as MMMYYYY to match the MONYY7. format in SAS.

If you need the month in uppercase and year in two digits like MAY24, you can adjust the date_format function:


df_formatted = df.withColumn(“formatted_date”, date_format(to_date(col(“olddate”)), “MMMYY”).upper())
This approach covers the translation of extracting and formatting date parts from a datetime value in PySpark SQL, providing a similar functionality to the SAS put(datepart(olddate), MONYY7.)