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:-

Function Name	Description	Example Usage	Result
SYSDATE	Returns the current date and time.	SELECT SYSDATE FROM dual;	04-Jun-24 12:34:56 PM
CURRENT_DATE	Returns the current date in the session time zone.	SELECT CURRENT_DATE FROM dual;	04-Jun-24
CURRENT_TIMESTAMP	Returns the current date and time in the session time zone.	SELECT CURRENT_TIMESTAMP FROM dual;	04-JUN-24 12:34:56.123456 PM -05:00
SYSTIMESTAMP	Returns the current date and time with time zone.	SELECT SYSTIMESTAMP FROM dual;	04-JUN-24 12:34:56.123456 PM -05:00
EXTRACT	Extracts and returns a specified part of a date (e.g., year).	SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;	2024
TRUNC	Truncates a date to the specified unit (e.g., month).	SELECT TRUNC(SYSDATE, ‘MM’) FROM dual;	01-Jun-24
ROUND	Rounds a date to the specified unit (e.g., month).	SELECT ROUND(SYSDATE, ‘MM’) FROM dual;	01-Jul-24
ADD_MONTHS	Adds a specified number of months to a date.	SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;	04-Aug-24
MONTHS_BETWEEN	Returns the number of months between two dates.	SELECT MONTHS_BETWEEN(SYSDATE, ’01-MAY-24′) FROM dual;	1.129032258
NEXT_DAY	Returns the date of the next specified day of the week.	SELECT NEXT_DAY(SYSDATE, ‘MONDAY’) FROM dual;	10-Jun-24
LAST_DAY	Returns the last day of the month for a given date.	SELECT LAST_DAY(SYSDATE) FROM dual;	30-Jun-24
NEW_TIME	Converts a date from one time zone to another.	SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM dual;	04-Jun-24 9:34:56 AM
TO_DATE	Converts a string to a date.	SELECT TO_DATE(‘2024-06-04’, ‘YYYY-MM-DD’) FROM dual;	04-Jun-24
TO_CHAR	Converts a date to a string in a specified format.	SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM dual;	2024-06-04
SYSDATE + n	Adds n days to the current date.	SELECT SYSDATE + 10 FROM dual;	14-Jun-24
SYSDATE – n	Subtracts n days from the current date.	SELECT SYSDATE – 10 FROM dual;	25-May-24
DATE ‘YYYY-MM-DD’	Specifies a date literal in ANSI date format.	SELECT DATE ‘2024-06-04’ FROM dual;	04-Jun-24
INTERVAL	Specifies a period of time.	SELECT SYSDATE + INTERVAL ‘1’ MONTH FROM dual;	04-Jul-24
DBTIMEZONE	Returns the database time zone.	SELECT DBTIMEZONE FROM dual;
SESSIONTIMEZONE	Returns the session time zone.	SELECT SESSIONTIMEZONE FROM dual;

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

Function Name	Description	Example Usage	Result
current_date	Returns the current date.	SELECT current_date;	2024-06-04 (if today is June 4, 2024)
current_timestamp	Returns the current timestamp.	SELECT current_timestamp;	2024-06-04 12:34:56
unix_timestamp	Returns current Unix timestamp in seconds.	SELECT unix_timestamp();	1654257296 (if current timestamp)
from_unixtime	Converts Unix timestamp to string in the specified format.	SELECT from_unixtime(1654257296);	2024-06-04 12:34:56
to_date	Extracts date part from a timestamp string.	SELECT to_date(‘2024-06-04 12:34:56’);	2024-06-04
year	Extracts the year from a date or timestamp.	SELECT year(‘2024-06-04’);	2024
month	Extracts the month from a date or timestamp.	SELECT month(‘2024-06-04’);	6
day	Extracts the day from a date or timestamp.	SELECT day(‘2024-06-04’);	4
hour	Extracts the hour from a timestamp.	SELECT hour(‘2024-06-04 12:34:56’);	12
minute	Extracts the minute from a timestamp.	SELECT minute(‘2024-06-04 12:34:56’);	34
second	Extracts the second from a timestamp.	SELECT second(‘2024-06-04 12:34:56’);	56
add_months	Adds a specified number of months to a date or timestamp.	SELECT add_months(‘2024-06-04’, 2);	2024-08-04
months_between(date1, date2)	Calculates the number of months between two dates.	select months_between(current_date, ‘2023-01-01’);	5 (assuming today is June 5th, 2024)
datediff	Returns the number of days between two dates.	SELECT datediff(‘2024-06-04’, ‘2024-05-01’);	34
date_add	Adds a specified number of days to a date.	SELECT date_add(‘2024-06-04’, 10);	2024-06-14
date_sub	Subtracts a specified number of days from a date.	SELECT date_sub(‘2024-06-04’, 10);	2024-05-25
last_day	Returns the last day of the month which the date belongs to.	SELECT last_day(‘2024-06-04’);	2024-06-30
Alternative for last_day		date_add(date_sub(current_date, 1), interval ‘1 month’) – interval ‘1 day’	2024-05-31 (assuming today is June 5th, 2024)
next_day	Returns the first date which is the specified day of the week.	SELECT next_day(‘2024-06-04’, ‘MON’);	2024-06-10 (next Monday from June 4, 2024)
trunc	Truncates a date or timestamp to the specified unit (e.g., month).	SELECT trunc(‘2024-06-04’, ‘MM’);	2024-06-01 (truncates to the start of month)
date_format	Formats a date or timestamp into a string.	SELECT date_format(‘2024-06-04’, ‘yyyy-MM’);	2024-06
2024-06-04′ used in select here is Date values not a string

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.)