DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:
SQL> SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
FROM employees;
hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
Wondering how 30 seconds can be added to a timestamp, here is a typical example.
SQL>select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Seems pretty simple right ? :) here is a couple more that I did;
Hope it was helpful, enjoy.
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:
SQL> SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
FROM employees;
hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440,sysdate +1/86400 from dual;
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
Wondering how 30 seconds can be added to a timestamp, here is a typical example.
SQL>select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Seems pretty simple right ? :) here is a couple more that I did;
| Description | Date Expression |
|---|---|
| Now | SYSDATE |
| Tomorow/ next day | SYSDATE + 1 |
| Seven days from now | SYSDATE + 7 |
| One hour from now | SYSDATE + 1/24 |
| Three hours from now | SYSDATE + 3/24 |
| An half hour from now | SYSDATE + 1/48 |
| 10 minutes from now | SYSDATE + 10/1440 |
| 30 seconds from now | SYSDATE + 30/86400 |
| Tomorrow at 12 midnight | TRUNC(SYSDATE + 1) |
| Tomorrow at 8 AM | TRUNC(SYSDATE + 1) + 8/24 |
| Next Monday at 12:00 noon | NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24 |
| First day of the month at 12 midnight | TRUNC(LAST_DAY(SYSDATE ) + 1) |
| The next Monday, Wednesday or Friday at 9 a.m | TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24) |
Hope it was helpful, enjoy.
Thanks for inspiring me to offer a utility on LiveSQL: https://livesql.oracle.com/apex/livesql/file/content_DZV7K4ZCO41HJQCGHFFPLVT38.html
ReplyDeleteHow to add 1 hour 1 second at a time. Thank you.
ReplyDeleteThe way presented here is very... not very neat ;)
Deletemanoj... well... generalize your time to whatever is suitable, probably seconds in your case.
The neat way is:
select sysdate NOW, sysdate + interval '3601' second from dual;
or...
select sysdate NOW, sysdate + interval '1' hour + interval '1' second from dual;
ReplyDeleteHow to add n(deration from another column) minitues..?
select START_TIME ACTIVITY_START_TIME ,
ACTIVITY_START_TIME + interval 'n' minute ACTIVITY_END_TIME from DUAL;
If n is the value of other column
DeleteSELECT to_char((to_date('08:00:00','HH24:MI:SS') + (1/1440*n) ),'HH24:MI:SS') INTO inicio FROM dual;