Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 14 total |
Extracting Milliseconds from TimeStamp |
Sun, Feb 24 2008 8:25 PM | Permanent Link |
"Adam H." | Hi,
I was wondering if anyone can tell me how to extract the milliseconds from a TimeStamp field via SQL in DBISam 4? If I subtract one timestamp from another the results are in ms, but I can't figure out how to get the total milliseconds in general (ie, not just an extraction, but the total milliseconds adding from midnight to that time). Thanks & Regards Adam. |
Mon, Feb 25 2008 3:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< I was wondering if anyone can tell me how to extract the milliseconds from a TimeStamp field via SQL in DBISam 4? If I subtract one timestamp from another the results are in ms, but I can't figure out how to get the total milliseconds in general (ie, not just an extraction, but the total milliseconds adding from midnight to that time). >> You can use the *FROMMSECS functions for that purpose: http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8 they're at the bottom of that topic. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Feb 25 2008 5:50 PM | Permanent Link |
"Adam H." | Hi Tim,
Thanks for your reply... > You can use the *FROMMSECS functions for that purpose: > > http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8 > > they're at the bottom of that topic. When I try the following: select starttime, endtime, MSECSFROMMSECS(starttime) from records I get the error: DBISAM Engine Error # 11949 SQL parsing error - Expected NULL, SmallInt, Word, AutoInc, Integer, LargeInt, Currenty, Float, or BCD expression but instead found starttime in SELECT SQL statement.... StartTime is at "TIME" field. In addition to this, the manual states "Takes milliseconds and returns the number of milliseconds (as a remainder of the above years, days, hours, minutes, and seconds, not as an absolute)." As such, won't it simply return the milliseconds within the time since the previous 'Second' time, instead of the milliseconds since Midnight? Thanks & Regards Adam. |
Wed, Feb 27 2008 6:18 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< When I try the following: select starttime, endtime, MSECSFROMMSECS(starttime) from records I get the error: >> You can't use those functions directly on a time. They're for use on the resulting milliseconds (numeric value) that result from subtracting two time values. << As such, won't it simply return the milliseconds within the time since the previous 'Second' time, instead of the milliseconds since Midnight? >> If you want to just extract the milliseconds from a time field, use the EXTRACT function. See the same Functions area of the manual for the syntax. My apologies for the confusion - I was under the impression that you wanted to get the elapsed time information for two times. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 27 2008 7:21 PM | Permanent Link |
"Adam H." | Good morning Tim,
Thanks again for your reply. > If you want to just extract the milliseconds from a time field, use the > EXTRACT function. See the same Functions area of the manual for the > syntax. Unfortuantly this too will not do what we're after. This extracts the milliseconds from the last whole minute. If the time is 08:15, I'm of the understanding that the stored value for the Time field in the database would be 2970000 (as the stored value is in milliseconds from midnight). What I'm trying to do is retrieve that actual value. (Either that, or I'm happy to return the number of seconds or even minutes of the time since midnight, so either 495 minutes or 29,700 seconds would also be adequate as a result. The EXTRACT function does not do this, in that an extract function on 08:15 would return: 0 milliseconds 0 seconds 15 minutes 8 hours I can see that if I use something like the following: select starttime, endtime, (((extract(hour from endtime) * 60 + extract (minute from endtime)) * 60 ) + extract(second from endtime)* 100 as EndMilliseconds from records I can get the information that I'm after - but I thought that if the data was stored in milliseconds to start with, that their should be an easier way to get the information direct. Is there a way to grab the actual value that is stored from the table via SQL natively? Thanks & Regards Adam. |
Wed, Feb 27 2008 8:44 PM | Permanent Link |
"Jeff Cook" | Adam H. wrote:
> > If the time is 08:15, I'm of the understanding that the stored value > for the Time field in the database would be 2970000 (as the stored > value is in milliseconds from midnight). Adam Just so you don't need to try it, CAST doesn't work (in v3.30 anyway) SELECT CURRENT_TIMESTAMP AS TimeStamp , CAST(CURRENT_TIMESTAMP * 1000 AS INTEGER) AS MilliSecs FROM MyTable TOP 1 I had thought that I was going to solve your problem with a flash of brilliance but ... Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Wed, Feb 27 2008 10:37 PM | Permanent Link |
"Adam H." | Hi Jeff,
Long time no talk! How's it going over there across the Pacific? > Just so you don't need to try it, CAST doesn't work (in v3.30 anyway) Thanks for the info. Doesn't work in v4 either. (Tried it > I had thought that I was going to solve your problem with a flash of > brilliance but ... Yeah - it's got me cornered. I thought it was going to be an easy one, but it's posing to be a real beauty at the moment! Cheers Adam. |
Thu, Feb 28 2008 6:56 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< Unfortuantly this too will not do what we're after. This extracts the milliseconds from the last whole minute. If the time is 08:15, I'm of the understanding that the stored value for the Time field in the database would be 2970000 (as the stored value is in milliseconds from midnight). What I'm trying to do is retrieve that actual value. (Either that, or I'm happy to return the number of seconds or even minutes of the time since idnight, so either 495 minutes or 29,700 seconds would also be adequate as a result. >> Ahh, okay. In that case, use something like this: SELECT CAST(<TimeStampColumn> AS TIME)-CAST('00:00:00' AS TIME) FROM MyTable Just skip the first CAST if the column is already a TIME column and not a TIMESTAMP column. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Feb 28 2008 3:40 PM | Permanent Link |
"Jeff Cook" | Tim Young [Elevate Software] wrote:
> SELECT CAST(<TimeStampColumn> AS TIME)-CAST('00:00:00' AS TIME) > FROM MyTable Odd (to me anyway) In DBSYS v3.30 this:- SELECT CAST(CURRENT_TIMESTAMP AS TIME)-CAST('00:00:00' AS TIME) FROM MyTable TOP 1 .... always gives the same number of milliseconds UNLESS you Unprepare each time you run it. If this is true in v4.x, then it could be a trap for young players! Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Thu, Feb 28 2008 3:45 PM | Permanent Link |
"Jeff Cook" | Adam H. wrote:
> > Long time no talk! How's it going over there across the Pacific? > It is our quiet season here, despite the clear blue sky and the crystal clear water of the lagoon ... I sound like a travel brochure. It's quiet because our major markets for visitors are the Aussies and the Kiwis and theoretically their weather is pretty good this time of year. And we are also supposed to be in the hurricane season (touch wood). Quiet means more time shackled to the computer -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |