Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
compare dates
- Thread starter vintl
- Start date
Questions:
1) What platform are you on? What hardware, what OS?
2) What version of Perl?
3) What database are you using?
4) What is the datatype of the date field you want
to compare with today's date? Hardy Merrill
Mission Critical Linux, Inc.
1) What platform are you on? What hardware, what OS?
2) What version of Perl?
3) What database are you using?
4) What is the datatype of the date field you want
to compare with today's date? Hardy Merrill
Mission Critical Linux, Inc.
- Thread starter
- #3
I'm not sure this will work - it's been quite a while since I've done anything with MySQL. I'm looking at the book "MySQL" by Paul Dubois for reference - on p.538 it describes a DATE_SUB function - that stands for DATE SUBTRACT.
If you want to subtract 1 day from today, I think you could use this:
SELECT DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
You'll have to check out the other date/time functions that MySQL offers if you need to do other things.
But since your question was how to find the *difference* between 2 dates, one MySQL function I can see that might help is PERIOD_DIFF(period1, period2), and that gives you the difference in months. Another useful function might be TO_DAYS - use that to convert the dates into days since year 0, and then take the difference:
SELECT TO_DAYS(SYSDATE()) - TO_DAYS(TRAN_DATE) AS DATE_DIFF
FROM MY_TABLE
and that will(I think?) will give you the difference in days.
Another that may be helpful is UNIX_TIMESTAMP - if you give that function a date or datetime string, it will return the number of seconds since the Epoch and that date - you could so something like this:
SELECT UNIX_TIMESTAMP(SYSDATE()) as TODAY,
UNIX_TIMESTAMP(TRAN_DATE) as TRAN_DATE
then you could subtract TRAN_DATE from TODAY to get the number of seconds between the 2 dates, and then you'd have to convert that into days, hours, minutes, seconds. There are quite a few ways you could go about it - these are just a few ideas. You should check out for current reference information on date math functions. In fact, I just went there myself and found a section titled
3.3.4.5 Date Calculations
HTH. Hardy Merrill
Mission Critical Linux, Inc.
If you want to subtract 1 day from today, I think you could use this:
SELECT DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
You'll have to check out the other date/time functions that MySQL offers if you need to do other things.
But since your question was how to find the *difference* between 2 dates, one MySQL function I can see that might help is PERIOD_DIFF(period1, period2), and that gives you the difference in months. Another useful function might be TO_DAYS - use that to convert the dates into days since year 0, and then take the difference:
SELECT TO_DAYS(SYSDATE()) - TO_DAYS(TRAN_DATE) AS DATE_DIFF
FROM MY_TABLE
and that will(I think?) will give you the difference in days.
Another that may be helpful is UNIX_TIMESTAMP - if you give that function a date or datetime string, it will return the number of seconds since the Epoch and that date - you could so something like this:
SELECT UNIX_TIMESTAMP(SYSDATE()) as TODAY,
UNIX_TIMESTAMP(TRAN_DATE) as TRAN_DATE
then you could subtract TRAN_DATE from TODAY to get the number of seconds between the 2 dates, and then you'd have to convert that into days, hours, minutes, seconds. There are quite a few ways you could go about it - these are just a few ideas. You should check out for current reference information on date math functions. In fact, I just went there myself and found a section titled
3.3.4.5 Date Calculations
HTH. Hardy Merrill
Mission Critical Linux, Inc.
- Status
Similar threads
