Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 11 to 20 of 30 total

How to add variable x days to a TIMESTAMP?

Tue, Nov 28 2017 9:50 AM	Permanent Link
Rolf Frei eicom GmbH	What is the NULL TIMESTAMP in EDB? In Delphi it is 1899-12-30.
Tue, Nov 28 2017 10:19 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Rolf Just tried cast(null as timestamp) in a query and in EDBManager it shows as NULL Roy Lambert
Tue, Nov 28 2017 10:53 AM	Permanent Link
Rolf Frei eicom GmbH	Yes that's normal andexpected, but it is not what I mean. What is the lowest TIMESTAMP EDB can handle? 0 in delphi gives 1899-01-01, what is EDB's internal DATE for 0 or whatever EDB uses internally to store TIMESTAMP's?
Wed, Nov 29 2017 5:00 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Rolf >Yes that's normal andexpected, but it is not what I mean. What is the lowest TIMESTAMP EDB can handle? 0 in delphi gives 1899-01-01, what is EDB's internal DATE for 0 or whatever EDB uses internally to store TIMESTAMP's? I don't know, I might have asked Tim once but if I did and he answered I've forgotten. It doesn't really matter though. It did with DBISAM because you could do arithmetic on dates / times since they were held as floats and could be accessed as such. That's not possible in ElevateDB and it's translated seamlessly at the Delphi / ElavateDB boundary. Or, are you commenting on the difference between the way Delphi and ElevateDB select the date when a stand alone time is converted to a timestamp? If so then its a matter of the different datatypes. In Delphi time, date, timestamp all resolve to float. That makes the conversion from time to timestamp simply a matter of presentation, no data change necessary. Not so in ElevateDB where they are distinct datatypes and to move fom time to timestamp means adding a date element rather than just presenting the zero that's already there. Using CURRENT_DATE for that I, personally, find more palatable than 1899-01-01, but I accept its different and needs to be handled. Roy
Wed, Nov 29 2017 10:20 AM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Rolf, << I have x Days (INTEGER) and also a Time (TIME) value. I want now combine the two to get an interval Day to MSeconds. My Time may be "01:35:10.456" and the days value is 5. Now I want get an interval from that like this "5 01:35:10.456". >> I'm away from the office right now because my mother passed away last week and we're attending the service and seeing to her affairs. When I'm back on Friday or Saturday, I will go through this thread and answer your questions. Most of what you want is in ElevateDB, and no, doing date/time calculations is not easier than the INTERVAL handling. It just takes some getting used to, but the INTERVAL functionality is much more powerful than anything prior. Tim Young Elevate Software www.elevatesoft.com
Wed, Nov 29 2017 10:43 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim >I'm away from the office right now because my mother passed away last week My sympathies to you Roy
Fri, Dec 1 2017 10:34 AM	Permanent Link
Rolf Frei eicom GmbH	<< I'm away from the office right now because my mother passed away >> My heartfelt sympathies go to you and your family. Thanks for your time to give me some answers if you get the time. I'm interested to see if there is a simpler methode to do, than what I have already done. Tanks and Regards Rolf
Mon, Dec 4 2017 12:31 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	<< My sympathies to you >> Thanks Roy. She was the last, it's just me and my brother now, so it's going to take some time to get used to... Tim Young Elevate Software www.elevatesoft.com
Mon, Dec 4 2017 12:33 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	<< My heartfelt sympathies go to you and your family. >> Thanks Rolf. << Thanks for your time to give me some answers if you get the time. I'm interested to see if there is a simpler methode to do, than what I have already done. >> I will post something this afternoon. I just need to re-read the problem descriptions and make sure that I've got it all straight. Tim Young Elevate Software www.elevatesoft.com
Mon, Dec 4 2017 12:41 PM	Permanent Link
Rolf Frei eicom GmbH	I think I know now how to do what I want. Below you can see my new version of the function "FractionToTimeInterval", which doesn't need anymore that many casts to a string and back to an interval. What I did found out so far: - My searched "Null" DATE is '0001-01-01' - Combining a DATE and TIME Field into a TIMESTAMP can be done like this: CAST(TmpDate AS TIMESTAMP) + (TmpTime - TIME '00:00:00.000')) It would be nice to get a builtin Function which does this for us like "TimeStamp(DATE, TIME)" as in some other DBMS. Here is the better and more elegant version of my above function: CREATE FUNCTION "FractionToTimeInterval" (IN "ATimeFraction" FLOAT) RETURNS INTERVAL DAY TO MSECOND BEGIN DECLARE NullTimeStamp TIMESTAMP DEFAULT '0001-01-01 00:00:00.000'; DECLARE NullDate DATE DEFAULT '0001-01-01'; DECLARE NullTime TIME DEFAULT '00:00:00.000'; DECLARE Days INTEGER; DECLARE IDays INTERVAl DAY; DECLARE TmpDate DATE; DECLARE TmpTime TIME; SET Days = TRUNC(ATimeFraction); SET IDays = CAST(Days AS INTERVAL DAY); SET TmpDate = NullDate + IDays; SET TmpTime = FractionToTime(ATimeFraction - Days); RETURN (CAST(TmpDate AS TIMESTAMP) + (TmpTime - NullTime)) - NullTimeStamp; END Tim, is this the right way to do this or is there still something better?

« Previous Page	Page 2 of 3	Next Page »
Jump to Page: 1 2 3