Oracle how to add minutes to a timestamp

Question

I need to add 30 minutes to values in a Oracle date column   I do this in my SELECT statement by specifying   to char date and time     000694   31   which works fine most of the time   But not when the time is on the AM PM border   For example  adding 30 minutes to 12 30  which is  PM  returns 1 00 which is AM  The answer I expect is 13 00   What s the correct way to do this

User · Answer

UPDATE  TABLE   SET DATE FIELD   CURRENT TIMESTAMP   interval  48  minute  WHERE         Where interval is one of    YEAR MONTH DAY HOUR MINUTE SECOND

User · Answer

Based on what you re asking for  you want the HH24 MI format for to char

User · Answer

from http   www orafaq com faq how does one add a day hour minute second to a date value  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  SQL gt  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

User · Answer

like that very easily  i added 10 minutes to system date and always in preference  use the Db server functions not custom one    select to char sysdate   NUMTODSINTERVAL 10  MINUTE    DD MM YYYY HH24 MI SS   from dual

User · Answer

Be sure that Oracle understands that the starting time is PM  and to specify the HH24 format mask for the final output   SELECT to char  to date  12 40 PM    HH MI AM      1 24 60    30    HH24 MI   as time   FROM dual  TIME --------- 13 10   Note  the  AM  in the HH MI is just the placeholder for the AM PM meridian indicator  Could be also  PM

User · Answer

SELECT to char sysdate    1 24 60    30   dd mm yy HH24 MI am   from dual    simply you can use this with various date format

User · Answer

Based on what you re asking for  you want the HH24 MI format for to char

User · Answer

All of the other answers are basically right but I don t think anyone s directly answered your original question   Assuming that  date and time  in your example is a column with type DATE or TIMESTAMP  I think you just need to change this   to char date and time     000694   31     to this   to char date and time     000694   31    DD-MON-YYYY HH24 MI     It sounds like your default date format uses the  HH  code for the hour  not  HH24    Also  I think your constant term is both confusing and imprecise   I guess what you did is calculate that   000694  is about the value of a minute  and you are multiplying it by the number of minutes you want to add  31 in the example  although you said 30 in the text    I would also start with a day and divide it into the units you want within your code   In this case   1 48  would be 30 minutes  or if you wanted to break it up for clarity  you could write    1 24     1 2      This would avoid rounding errors  except for those inherent in floating point which should be meaningless here  and is clearer  at least to me

User · Answer

All of the other answers are basically right but I don t think anyone s directly answered your original question   Assuming that  date and time  in your example is a column with type DATE or TIMESTAMP  I think you just need to change this   to char date and time     000694   31     to this   to char date and time     000694   31    DD-MON-YYYY HH24 MI     It sounds like your default date format uses the  HH  code for the hour  not  HH24    Also  I think your constant term is both confusing and imprecise   I guess what you did is calculate that   000694  is about the value of a minute  and you are multiplying it by the number of minutes you want to add  31 in the example  although you said 30 in the text    I would also start with a day and divide it into the units you want within your code   In this case   1 48  would be 30 minutes  or if you wanted to break it up for clarity  you could write    1 24     1 2      This would avoid rounding errors  except for those inherent in floating point which should be meaningless here  and is clearer  at least to me

User · Answer

In addition to being able to add a number of days to a date  you can use interval data types assuming you are on Oracle 9i or later  which can be somewhat easier to read   SQL gt  ed Wrote file afiedt buf SELECT sysdate  sysdate   interval  30  minute FROM dual SQL gt     SYSDATE              SYSDATE INTERVAL 30  -------------------- -------------------- 02-NOV-2008 16 21 40 02-NOV-2008 16 51 40

User · Answer

To edit Date in oracle you can try     select to char  lt columnName gt    5   24   30    24   60               DD MM RRRR hh mi AM   AS  lt logicalName gt  from  lt tableName gt

User · Answer

If the data type of the field is date or timestamp  Oracle should always give the correct result if you add the correct number given in number of days  or a the correct fraction of a day in your case   So if you are trying to bump the value in 30 minutes  you should use     select field   0 5 24 from table    Based on the information you provided  I believe this is what you tried to do and I am quite sure it works

User · Answer

Can we not use this  SELECT date and time   INTERVAL  20 00  MINUTE TO SECOND FROM dual    I am new to this domain

User · Answer

I prefer using an interval literal for this  because interval  30  minute or interval  5  second is a lot easier to read then 30    24   60  or 5    24   60   69    e g     some date   interval  2  hour some date   interval  30  minute some date   interval  5  second some date   interval  2  day   You can also combine several units into one expression    some date   interval  2 3 06  day to minute    Adds 2 days  3 hours and 6 minutes to the date value  The above is also standard SQL and also works in several other DBMS    More details in the manual  https   docs oracle com database 121 SQLRF sql elements003 htm SQLRF00221

User · Answer

I prefer using an interval literal for this  because interval  30  minute or interval  5  second is a lot easier to read then 30    24   60  or 5    24   60   69    e g     some date   interval  2  hour some date   interval  30  minute some date   interval  5  second some date   interval  2  day   You can also combine several units into one expression    some date   interval  2 3 06  day to minute    Adds 2 days  3 hours and 6 minutes to the date value  The above is also standard SQL and also works in several other DBMS    More details in the manual  https   docs oracle com database 121 SQLRF sql elements003 htm SQLRF00221

User · Answer

Oracle now has new built in functions to do this  select systimestamp START TIME  systimestamp   NUMTODSINTERVAL 30   minute   end time from dual

User · Answer

Be sure that Oracle understands that the starting time is PM  and to specify the HH24 format mask for the final output   SELECT to char  to date  12 40 PM    HH MI AM      1 24 60    30    HH24 MI   as time   FROM dual  TIME --------- 13 10   Note  the  AM  in the HH MI is just the placeholder for the AM PM meridian indicator  Could be also  PM

User · Answer

SELECT to char sysdate    1 24 60    30   dd mm yy HH24 MI am   from dual    simply you can use this with various date format

User · Answer

All of the other answers are basically right but I don t think anyone s directly answered your original question   Assuming that  date and time  in your example is a column with type DATE or TIMESTAMP  I think you just need to change this   to char date and time     000694   31     to this   to char date and time     000694   31    DD-MON-YYYY HH24 MI     It sounds like your default date format uses the  HH  code for the hour  not  HH24    Also  I think your constant term is both confusing and imprecise   I guess what you did is calculate that   000694  is about the value of a minute  and you are multiplying it by the number of minutes you want to add  31 in the example  although you said 30 in the text    I would also start with a day and divide it into the units you want within your code   In this case   1 48  would be 30 minutes  or if you wanted to break it up for clarity  you could write    1 24     1 2      This would avoid rounding errors  except for those inherent in floating point which should be meaningless here  and is clearer  at least to me

User · Answer

like that very easily  i added 10 minutes to system date and always in preference  use the Db server functions not custom one    select to char sysdate   NUMTODSINTERVAL 10  MINUTE    DD MM YYYY HH24 MI SS   from dual

User · Answer

If the data type of the field is date or timestamp  Oracle should always give the correct result if you add the correct number given in number of days  or a the correct fraction of a day in your case   So if you are trying to bump the value in 30 minutes  you should use     select field   0 5 24 from table    Based on the information you provided  I believe this is what you tried to do and I am quite sure it works

User · Answer

UPDATE  TABLE   SET DATE FIELD   CURRENT TIMESTAMP   interval  48  minute  WHERE         Where interval is one of    YEAR MONTH DAY HOUR MINUTE SECOND

User · Answer

Based on what you re asking for  you want the HH24 MI format for to char

User · Answer

Oracle now has new built in functions to do this  select systimestamp START TIME  systimestamp   NUMTODSINTERVAL 30   minute   end time from dual

User · Answer

Be sure that Oracle understands that the starting time is PM  and to specify the HH24 format mask for the final output   SELECT to char  to date  12 40 PM    HH MI AM      1 24 60    30    HH24 MI   as time   FROM dual  TIME --------- 13 10   Note  the  AM  in the HH MI is just the placeholder for the AM PM meridian indicator  Could be also  PM

User · Answer

If the data type of the field is date or timestamp  Oracle should always give the correct result if you add the correct number given in number of days  or a the correct fraction of a day in your case   So if you are trying to bump the value in 30 minutes  you should use     select field   0 5 24 from table    Based on the information you provided  I believe this is what you tried to do and I am quite sure it works

User · Answer

In addition to being able to add a number of days to a date  you can use interval data types assuming you are on Oracle 9i or later  which can be somewhat easier to read   SQL gt  ed Wrote file afiedt buf SELECT sysdate  sysdate   interval  30  minute FROM dual SQL gt     SYSDATE              SYSDATE INTERVAL 30  -------------------- -------------------- 02-NOV-2008 16 21 40 02-NOV-2008 16 51 40

User · Answer

Can we not use this  SELECT date and time   INTERVAL  20 00  MINUTE TO SECOND FROM dual    I am new to this domain

User · Answer

from http   www orafaq com faq how does one add a day hour minute second to a date value  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  SQL gt  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

User · Answer

In addition to being able to add a number of days to a date  you can use interval data types assuming you are on Oracle 9i or later  which can be somewhat easier to read   SQL gt  ed Wrote file afiedt buf SELECT sysdate  sysdate   interval  30  minute FROM dual SQL gt     SYSDATE              SYSDATE INTERVAL 30  -------------------- -------------------- 02-NOV-2008 16 21 40 02-NOV-2008 16 51 40

User · Answer

To edit Date in oracle you can try     select to char  lt columnName gt    5   24   30    24   60               DD MM RRRR hh mi AM   AS  lt logicalName gt  from  lt tableName gt

User · Answer

Be sure that Oracle understands that the starting time is PM  and to specify the HH24 format mask for the final output   SELECT to char  to date  12 40 PM    HH MI AM      1 24 60    30    HH24 MI   as time   FROM dual  TIME --------- 13 10   Note  the  AM  in the HH MI is just the placeholder for the AM PM meridian indicator  Could be also  PM

User · Answer

In addition to being able to add a number of days to a date  you can use interval data types assuming you are on Oracle 9i or later  which can be somewhat easier to read   SQL gt  ed Wrote file afiedt buf SELECT sysdate  sysdate   interval  30  minute FROM dual SQL gt     SYSDATE              SYSDATE INTERVAL 30  -------------------- -------------------- 02-NOV-2008 16 21 40 02-NOV-2008 16 51 40

User · Answer

All of the other answers are basically right but I don t think anyone s directly answered your original question   Assuming that  date and time  in your example is a column with type DATE or TIMESTAMP  I think you just need to change this   to char date and time     000694   31     to this   to char date and time     000694   31    DD-MON-YYYY HH24 MI     It sounds like your default date format uses the  HH  code for the hour  not  HH24    Also  I think your constant term is both confusing and imprecise   I guess what you did is calculate that   000694  is about the value of a minute  and you are multiplying it by the number of minutes you want to add  31 in the example  although you said 30 in the text    I would also start with a day and divide it into the units you want within your code   In this case   1 48  would be 30 minutes  or if you wanted to break it up for clarity  you could write    1 24     1 2      This would avoid rounding errors  except for those inherent in floating point which should be meaningless here  and is clearer  at least to me

User · Answer

If the data type of the field is date or timestamp  Oracle should always give the correct result if you add the correct number given in number of days  or a the correct fraction of a day in your case   So if you are trying to bump the value in 30 minutes  you should use     select field   0 5 24 from table    Based on the information you provided  I believe this is what you tried to do and I am quite sure it works

User · Answer

Based on what you re asking for  you want the HH24 MI format for to char

[oracle] Oracle: how to add minutes to a timestamp?

Examples related to oracle

Examples related to date

Examples related to timestamp

Examples related to date-arithmetic