Date and Time manipulation in Oracle SQL, Apache Hive QL, Mysql

by | Jun 2, 2024 | SQL | 0 comments

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 NameDescriptionExample UsageResult
SYSDATEReturns the current date and time.SELECT SYSDATE FROM dual;04-Jun-24 12:34:56 PM
CURRENT_DATEReturns the current date in the session time zone.SELECT CURRENT_DATE FROM dual;04-Jun-24
CURRENT_TIMESTAMPReturns 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
SYSTIMESTAMPReturns the current date and time with time zone.SELECT SYSTIMESTAMP FROM dual;04-JUN-24 12:34:56.123456 PM -05:00
EXTRACTExtracts and returns a specified part of a date (e.g., year).SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;2024
TRUNCTruncates a date to the specified unit (e.g., month).SELECT TRUNC(SYSDATE, ‘MM’) FROM dual;01-Jun-24
ROUNDRounds a date to the specified unit (e.g., month).SELECT ROUND(SYSDATE, ‘MM’) FROM dual;01-Jul-24
ADD_MONTHSAdds a specified number of months to a date.SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;04-Aug-24
MONTHS_BETWEENReturns the number of months between two dates.SELECT MONTHS_BETWEEN(SYSDATE, ’01-MAY-24′) FROM dual;1.129032258
NEXT_DAYReturns the date of the next specified day of the week.SELECT NEXT_DAY(SYSDATE, ‘MONDAY’) FROM dual;10-Jun-24
LAST_DAYReturns the last day of the month for a given date.SELECT LAST_DAY(SYSDATE) FROM dual;30-Jun-24
NEW_TIMEConverts a date from one time zone to another.SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM dual;04-Jun-24 9:34:56 AM
TO_DATEConverts a string to a date.SELECT TO_DATE(‘2024-06-04’, ‘YYYY-MM-DD’) FROM dual;04-Jun-24
TO_CHARConverts a date to a string in a specified format.SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM dual;2024-06-04
SYSDATE + nAdds n days to the current date.SELECT SYSDATE + 10 FROM dual;14-Jun-24
SYSDATE – nSubtracts 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
INTERVALSpecifies a period of time.SELECT SYSDATE + INTERVAL ‘1’ MONTH FROM dual;04-Jul-24
DBTIMEZONEReturns the database time zone.SELECT DBTIMEZONE FROM dual;
SESSIONTIMEZONEReturns 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 NameDescriptionExample UsageResult
current_dateReturns the current date.SELECT current_date;2024-06-04 (if today is June 4, 2024)
current_timestampReturns the current timestamp.SELECT current_timestamp;2024-06-04 12:34:56
unix_timestampReturns current Unix timestamp in seconds.SELECT unix_timestamp();1654257296 (if current timestamp)
from_unixtimeConverts Unix timestamp to string in the specified format.SELECT from_unixtime(1654257296);2024-06-04 12:34:56
to_dateExtracts date part from a timestamp string.SELECT to_date(‘2024-06-04 12:34:56’);2024-06-04
yearExtracts the year from a date or timestamp.SELECT year(‘2024-06-04’);2024
monthExtracts the month from a date or timestamp.SELECT month(‘2024-06-04’);6
dayExtracts the day from a date or timestamp.SELECT day(‘2024-06-04’);4
hourExtracts the hour from a timestamp.SELECT hour(‘2024-06-04 12:34:56’);12
minuteExtracts the minute from a timestamp.SELECT minute(‘2024-06-04 12:34:56’);34
secondExtracts the second from a timestamp.SELECT second(‘2024-06-04 12:34:56’);56
add_monthsAdds 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)
datediffReturns the number of days between two dates.SELECT datediff(‘2024-06-04’, ‘2024-05-01’);34
date_addAdds a specified number of days to a date.SELECT date_add(‘2024-06-04’, 10);2024-06-14
date_subSubtracts a specified number of days from a date.SELECT date_sub(‘2024-06-04’, 10);2024-05-25
last_dayReturns the last day of the month which the date belongs to.SELECT last_day(‘2024-06-04’);2024-06-30
Alternative for last_daydate_add(date_sub(current_date, 1), interval ‘1 month’) – interval ‘1 day’2024-05-31 (assuming today is June 5th, 2024)
next_dayReturns 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)
truncTruncates 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_formatFormats 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

Written by HintsToday Team

Related Posts

Get the latest news

Subscribe to our Newsletter

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *