Home » SQL & PL/SQL » SQL & PL/SQL » display date by increment of one minute (Oracle 10g)
display date by increment of one minute [message #422082] Mon, 14 September 2009 04:47 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,

I want to display date by increment or decrement of one minute or one second. Please suggest me.

Regards,
Madhavi.
Re: display date by increment of one minute [message #422083 is a reply to message #422082] Mon, 14 September 2009 05:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
+/- 1/(24 * 60) -- min
+/- 1/(24 * 60 * 60) -- sec

By
Vamsi
Re: display date by increment of one minute [message #422084 is a reply to message #422082] Mon, 14 September 2009 05:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 select to_char(sysdate + 1/(24*60),'mi') from dual;
Re: display date by increment of one minute [message #422085 is a reply to message #422082] Mon, 14 September 2009 05:20 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
The explanation is that Oracle performs date calculations in units of days. One day has 24 hours and 24*60 (i.e. 1440) minutes. So in order to add one minute to a date you 'd have to take 1 over 1440 (24*60) and add that to your date.

MHE
Re: display date by increment of one minute [message #422087 is a reply to message #422082] Mon, 14 September 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can also use INTERVAL constant or NUMTODSINSTERVAL function:
SQL> select to_char(sysdate,'MI') minute,
  2         to_char(sysdate+interval '1' minute,'MI') "MINUTE+1",
  3         to_char(sysdate-numtodsinterval(1,'MINUTE'),'MI') "MINUTE-1"
  4  from dual
  5  /
MI MI MI
-- -- --
55 56 54

1 row selected.

Regards
Michel

Re: display date by increment of one minute [message #422167 is a reply to message #422082] Mon, 14 September 2009 22:59 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Guess I don't understand the question. Oracle normal dates are already at a granularity of a second. Let me supply this:

alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';

  1  select
  2   sysdate normal_granularity
  3  ,trunc(sysdate,'mi') minutes_granularity
  4  ,trunc(sysdate,'hh24') hours_granularity
  5  ,trunc(sysdate,'dd') days_granularity
  6  ,trunc(sysdate,'mm') months_granularity
  7  ,trunc(sysdate,'rrrr') years_granularity
  8* from dual
SQL> /

NORMAL_GRANULARITY   MINUTES_GRANULARITY  HOURS_GRANULARITY    DAYS_GRANULARITY     MONTHS_GRANULARITY   YEARS_GRANULARITY
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
14-sep-2009 23:57:44 14-sep-2009 23:57:00 14-sep-2009 23:00:00 14-sep-2009 00:00:00 01-sep-2009 00:00:00 01-jan-2009 00:00:00

SQL> 


Kevin
Previous Topic: DBA_MVIEWS Staleness showing ERROR
Next Topic: update salary for lowest paid employee in each dept
Goto Forum:
  


Current Time: Thu Dec 05 12:30:43 CST 2024