Skip to main content

How does one add a day/hour/minute/second to a date value?

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:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440,sysdate +1/86400 from dual;

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;


DescriptionDate Expression
NowSYSDATE
Tomorow/ next daySYSDATE + 1
Seven days from nowSYSDATE + 7
One hour from nowSYSDATE + 1/24
Three hours from nowSYSDATE + 3/24
An half hour from nowSYSDATE + 1/48
10 minutes from nowSYSDATE + 10/1440
30 seconds from nowSYSDATE + 30/86400
Tomorrow at 12 midnightTRUNC(SYSDATE + 1)
Tomorrow at 8 AMTRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noonNEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnightTRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.mTRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

Hope it was helpful, enjoy.

Comments

  1. Thanks for inspiring me to offer a utility on LiveSQL: https://livesql.oracle.com/apex/livesql/file/content_DZV7K4ZCO41HJQCGHFFPLVT38.html

    ReplyDelete
  2. How to add 1 hour 1 second at a time. Thank you.

    ReplyDelete
    Replies
    1. The way presented here is very... not very neat ;)

      manoj... 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;

      Delete

  3. How 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;

    ReplyDelete
    Replies
    1. If n is the value of other column

      SELECT to_char((to_date('08:00:00','HH24:MI:SS') + (1/1440*n) ),'HH24:MI:SS') INTO inicio FROM dual;

      Delete

Post a Comment

Popular posts from this blog

Using App Icons In Oracle Apex (Part1)

 Recently with the introduction of Apex 4.2 and later Apex 4.2.1 the default published  packaged applications comes with a sleek logo just at the left-hand corner of the the login screen. Fig 1 Fig 2 Fig 3 If u want to use these cool  sprites that come with the later releases as said in the the former. From the above diagram, looking into the css  inside the  i / or anywhere that your images are stored navigate to the cloud/app_theme/css/4_1.css You would find the img.appIcon.  class  which houses all the sprite launchpad logos in the Fig 1 Inside the css, locate ------App Icons Image-- img.appIcon.project_tracking{background-position:-256px 0}img.appIcon.solar{background-position:-320px 0}img.appIcon.website{background-position:-384px 0}img.appIcon.apex_service{background-position:-448px 0}img.appIcon.solar_red{background-position:-512px 0}img.appIcon.sales{background-position:-576px 0}img.appIcon.data_modeler{background-position:0 -64px}img.appIcon.group_calen

Disabling of Password Expiration in Oracle Apex(Internal Workspace Admin)

This post was inspired by a  question  on the OTN APEX forum, which contains how to  reset set the password of the  Oracle Internal Workspace Admin    and  Set the account never to expire  The first bullet has so many blogs  talk about how to reset the password of the Internal Workspace. However, i am more intrigued with the second . To start of with It is not advisable to never expire accounts since its rudimental for user to always renew their accounts  prior to expiration. The default expiration of an account is mostly 180 days so hey whats the point going to do this again after 180 days?? . There are two methods that can be used to achieve this  Generic Never expiration of all User accounts (This should never be practiced in a production Environment All database users are assigned to something called a PROFILE . The profile controls two aspects of the users database access – the system resources available to the user and th