Mid Point Between Two Dates
Moderator: NorbertKrupa
Mid Point Between Two Dates
Is there a function that I can use to calculate the mid-point between two dates?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Mid Point Between Two Dates
Hi jbaskin,
There isn't a function to do that, but you can find the midpoint between two dates pretty easily....
For instance, if you happen to be a big kid like me waiting for his birthday so that he’ll have an excuse to buy a new video game, you can run the following query to find out when your wait will be half over:
So that you can re-use and simplify the code you can create your own functions (one for dates and one for timestamps):
Here’s an example of using the new function MIDPOINT_TIMESTAMP:
Hope this helps!
There isn't a function to do that, but you can find the midpoint between two dates pretty easily....
For instance, if you happen to be a big kid like me waiting for his birthday so that he’ll have an excuse to buy a new video game, you can run the following query to find out when your wait will be half over:
Code: Select all
dbadmin=> SELECT TRUNC(SYSDATE) AS today,
dbadmin-> DATE '2012-09-19' my_birthday,
dbadmin-> TRUNC(SYSDATE) + (DATE '2012-09-19' - TRUNC(SYSDATE)) / 2 half_way_to_my_birthday;
today | my_birthday | half_way_to_my_birthday
---------------------+-------------+-------------------------
2012-07-16 00:00:00 | 2012-09-19 | 2012-08-17 12:00:00
(1 row)
Code: Select all
dbadmin=> CREATE FUNCTION midpoint_date (date1 date, date2 date) RETURN DATE
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN (date1 + ((date2 - date1) / 2));
dbadmin-> END;
CREATE FUNCTION
Code: Select all
dbadmin=> CREATE FUNCTION midpoint_timestamp (timestamp1 timestamp, timestamp2 timestamp) RETURN TIMESTAMP
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN (timestamp1 + ((timestamp2 - timestamp1) / 2));
dbadmin-> END;
CREATE FUNCTION
Code: Select all
dbadmin=> SELECT midpoint_timestamp (TRUNC(SYSDATE), TIMESTAMP '2012-09-19');
midpoint_timestamp
---------------------
2012-08-17 12:00:00
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Mid Point Between Two Dates
FYI...
Another way the function to find the midpoint between two timestamps can be written is by extracting the epoch from the upper and lower timestamps:
This method is slightly faster than the method used in my previous post.
Also, if you prefer, you do not need to create two functions (one to handle DATE and one for TIMESTAMP data types). You can simply cast the TIMESTAMP to a DATE:
Another way the function to find the midpoint between two timestamps can be written is by extracting the epoch from the upper and lower timestamps:
Code: Select all
CREATE OR REPLACE FUNCTION mid_date(s timestamp, e timestamp)
RETURN TIMESTAMP
AS BEGIN
RETURN TO_TIMESTAMP((EXTRACT(epoch from s) + EXTRACT(epoch from e))/2.0);
END;
Also, if you prefer, you do not need to create two functions (one to handle DATE and one for TIMESTAMP data types). You can simply cast the TIMESTAMP to a DATE:
Code: Select all
dbadmin=> SELECT midpoint_timestamp('2012-01-01', '2012-01-02')::DATE AS "Midpoint as a DATE",
dbadmin-> midpoint_timestamp('2012-01-01', '2012-01-02') AS "Midpoint as a TIMESTAMP";
Midpoint as a DATE | Midpoint as a TIMESTAMP
--------------------+-------------------------
2012-01-01 | 2012-01-01 12:00:00
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.