OraFAQ Forum: SQL & PL/SQL » Convert date & timestamp into seconds

Home » SQL & PL/SQL » SQL & PL/SQL » Convert date & timestamp into seconds (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert date & timestamp into seconds [message #604463]

Tue, 31 December 2013 02:38

Nshan
Messages: 62
Registered: February 2009

Member

Hi All,

Below is my requirement.

I have one date column say eta_column. Basically i need to enqueue the message into oracle queue with below delay time calculation,

If i am enqueueing at 10/14/2013 1:32:06 PM then my delay should also expire at trunc(eta_column) + timestamp should be the timestamp of enqueue time 1:32:06 PM

Ex : Enq_time = 10/14/2013 1:32:06 PM
Eta_column = 10/17/2013 1:00:00 AM
then Delay should expire @ 11/17/2013 1:32:00 PM not extactly the diff between Enq_time and Eta_column

Delay time should be pushed interms of seconds.

I can do a below calculation

(eta_column - trunc(sysdate))*24*60*60

But i am stuck at getting 1:32:06 PM value which should also be converted as seconds and added to previous value.

Could anyone please help me on this solution.

Thanks,
Nshan

Report message to a moderator

Re: Convert date & timestamp into seconds [message #604465 is a reply to message #604463]

Tue, 31 December 2013 03:03

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not clear what is the input and the output.

"If i am enqueueing at 10/14/2013 1:32:06 PM", is this an input or an output?
"then my delay should also expire at trunc(eta_column) + timestamp ", is this an input or an output? is "delay" an input or an output? is "timestamp" an input or an output?
"trunc(eta_column) + timestamp should be the timestamp of enqueue time 1:32:06 PM"; a timestamp is a date and a time, do you mean time instead of timestamp?
"Ex : Enq_time = 10/14/2013 1:32:06 PM
Eta_column = 10/17/2013 1:00:00 AM"
Enq_time is input or output? Eta_column is input or output? Where does come "1:00:00 AM3?
"I can do a below calculation" to get what?

What is the actual issue? Converting "1:32:06 PM" into seconds?

Report message to a moderator

Re: Convert date & timestamp into seconds [message #604466 is a reply to message #604463]

Tue, 31 December 2013 03:08

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Well, I couldn't clearly understand your requirement. However, by what you said :

Quote:

(eta_column - trunc(sysdate))*24*60*60

But i am stuck at getting 1:32:06 PM value which should also be converted as seconds and added to previous value.

I can suggest you not to use TRUNC. If you use TRUNC with DATE, see what happens -

SQL> select to_char(trunc(sysdate),'mm/dd/yyyy hh:mi:ss am') from dual;
 
TO_CHAR(TRUNC(SYSDATE),'MM/DD/
------------------------------
12/31/2013 12:00:00 am

So, the 'hh:mi:ss' part is defaulted to 12:00:00 am.

For correct calculation of seconds, you should leave it the way it is.

 
SQL> select to_char(sysdate,'mm/dd/yyyy hh:mi:ss am') from dual;
 
TO_CHAR(SYSDATE,'MM/DD/YYYYHH:
------------------------------
12/31/2013 01:03:07 am

The difference in two dates will be a number. You were right by multiplying 24*60*60 to get the difference in seconds. Just get rid of trunc.

SQL> WITH DATA AS(
  2  SELECT to_date('10/17/2013 1:00:00 AM','mm/dd/yyyy hh:mi:ss am') eta_column from dual)
  3  SELECT (SYSDATE - eta_column)*24*60*60 FROM DATA;
 
(SYSDATE-ETA_COLUMN)*24*60*60
-----------------------------
                      6480205

Report message to a moderator

Re: Convert date & timestamp into seconds [message #604475 is a reply to message #604466]

Tue, 31 December 2013 05:19

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Lalit Kumar B

The difference in two dates will be a number

... a number of DAYS (so that someone wouldn't think that it means "any number").

[Updated on: Tue, 31 December 2013 05:19]

Report message to a moderator

Re: Convert date & timestamp into seconds [message #604477 is a reply to message #604475]

Tue, 31 December 2013 05:45

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@LF : Thanks for adding few words, makes more sense now.

@OP : You might get a good idea at this demonstration by T.Kyte.

Report message to a moderator

Previous Topic:	Passing an collection of data as an input parameter to an procedure and insert them into a table?
Next Topic:	Help with regexp_substr

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:22:52 CDT 2024