| View previous topic :: View next topic |
| Author |
Message |
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
 Posted: Wed Jun 24, 2009 11:09 am Post subject: Timestamp Formatting in Spufi |
 |
|
Hi,
I have two Timestamp Variables on two different tables. I need to get the difference of them in Timesamp Format(atleast I need Days, hours, Minutes differences).
I tried:
a) B.UDT_TS - A.REC_TBL_NS_TS
This gave me in the format:
120148.241896
140249.574172
173804.785909
b) TIMESTAMP(B.UDT_TS - A.REC_TBL_NS_TS)
This gave me error
c) I used SECOND(B.UDT_TS - A.REC_TBL_NS_TS)
But it gives only differences upto 59 Seconds. If I have one day difference, it doesnt give.
Same is the case with Hour(B.UDT_TS - A.REC_TBL_NS_TS)
Can you please suggest a way so that I can get the O/P of differences of two Timestamp formats in the Date and Time format.
Thanks!! |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 24, 2009 11:25 am Post subject: |
|
|
IF you are on vsn8 you can use TIMESTAMPDIFF
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Wed Jun 24, 2009 2:14 pm Post subject: Re: Timestamp Formatting in Spufi |
|
|
| THRIVIKRAM wrote: | Hi,
I have two Timestamp Variables on two different tables. I need to get the difference of them in Timesamp Format(atleast I need Days, hours, Minutes differences).
I tried:
a) B.UDT_TS - A.REC_TBL_NS_TS
This gave me in the format:
120148.241896
140249.574172
173804.785909
b) TIMESTAMP(B.UDT_TS - A.REC_TBL_NS_TS)
This gave me error
c) I used SECOND(B.UDT_TS - A.REC_TBL_NS_TS)
But it gives only differences upto 59 Seconds. If I have one day difference, it doesnt give.
Same is the case with Hour(B.UDT_TS - A.REC_TBL_NS_TS)
Can you please suggest a way so that I can get the O/P of differences of two Timestamp formats in the Date and Time format.
Thanks!! |
Try looking in the book. A timestamp difference is returned as yyyymmddhhmmss.ssssss. In spufi the leading zeros would be suppressed so 120148.241896 is 12 hours 01 minutes 48 seconds and 241806 microseconds. |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Wed Jun 24, 2009 2:57 pm Post subject: |
|
|
| Quote: | | Can you please suggest a way so that I can get the O/P of differences of two Timestamp formats in the Date and Time format. |
This request doesn't make sense. You're mixing types. Date and time types are not the same as durations. What would be the expected result if the difference was exactly 1 day? If you want to try to plug that duration into a date and time format, do you want to see '0000-00-01' and '00.00.00'? 1 day is a valid duration, but it's out of context if you try to express it as a date.
CraigG gave you the correct duration format and dbz showed you another method to get the individual pieces. Don't try to express the results as a date and/or time because it won't make sense. |
|
| Back to top |
|
|
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
| Posted: Wed Jun 24, 2009 9:22 pm Post subject: |
|
|
Thanks CraigG. Actually, I could not properly interpret the result as you shown me now.
Anyways, I learned a new thing from DBZ too...
Thanks All for your prompt response!! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
