Re: Time in seconds

Posted in 1998

Dennis Pimple — Wed, 04 Mar 1998 08:03:15 -0700 — source: Informix-list mailing list archive (1991-1998)

--------------771BCA909ACD3BAEE14C085E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Warning: a much-too-long explanation follows, and you have to read the whole thing before trying any of it because there's a bug (I think) I describe at the end. Lee Wan Ling wrote: > Hi there, > I'm trying to get the current date in seconds format. Anyone > knows how I can do that? > Seconds from what? The beginning of time? The start of the year? The start of the day? Assuming the start of the year (which keeps the seconds to a manageable size: 366 days * 24 hours * 60 minutes * 60 seconds = 31,622,400, so I can store it in an INTERVAL SECOND(8) TO SECOND (SECOND(9) TO SECOND seems to be as high as we can go - see my sad story below). To get the number of seconds since the start of the year to the start of today, subtract today from the start of the year and multiply by 86,400 (24 * 60 * 60) SELECT 86400 * (TODAY - MDY( 1, 1, YEAR(TODAY) )) FROM systables WHERE tabname = "systables"; Make it an interval: INTERVAL (86400 * (TODAY - MDY( 1, 1, YEAR(TODAY) ))) SECOND(8) TO SECOND To get the interval since the start of the day: SELECT CURRENT - EXTEND( TODAY, YEAR TO DAY) ... So add the two INTERVALs together and you should have it. BUT ... trying this out on the engine at my immediate disposal (OnLine 7.24.UC1) gives me a lot of syntax errors when trying to convert INTERVAL with a lot of parentheses. No about of TEMP TABLE creation and selecting in and out to try to make it work bore any fruit. So, if you still need to know, let me know: 1) What "second #0" is (anything before the start of the decade would be too far in the past to deal with in INTEGER or INTERVAL sense) 2) Language of choice (I may be able to get a stored procedure to work, although they are cumbersome). A 4GL or NewEra calculation is likely possible, the results of which could be stored in the database. Sounds like another weekend project! //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 6300 S Syracuse Way Ste 205 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-779-4025 and do not necessarily //////////////// http://www.informix.com reflect those of my employer --------------771BCA909ACD3BAEE14C085E Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <HTML> Warning: a much-too-long explanation follows, and you have to read the whole thing before trying any of it because there's a bug (I think) I describe at the end. Lee Wan Ling wrote: <BLOCKQUOTE TYPE=CITE>Hi there,            I'm trying to get the current date in seconds format. Anyone knows how I can do that?  </BLOCKQUOTE> Seconds from what? The beginning of time? The start of the year? The start of the day? Assuming the start of the year (which keeps the seconds to a manageable size: 366 days * 24 hours * 60 minutes * 60 seconds = 31,622,400, so I can store it in an INTERVAL SECOND(8) TO SECOND (SECOND(9) TO SECOND seems to be as high as we can go - see my sad story below). To get the number of seconds since the start of the year to the start of today, subtract today from the start of the year and multiply by 86,400 (24 * 60 * 60) SELECT 86400 * (TODAY - MDY( 1, 1, YEAR(TODAY) )) FROM systables WHERE tabname = "systables"; Make it an interval: INTERVAL (86400 * (TODAY - MDY( 1, 1, YEAR(TODAY) ))) SECOND(8) TO SECOND To get the interval since the start of the day: SELECT CURRENT - EXTEND( TODAY, YEAR TO DAY) ... So add the two INTERVALs together and you should have it. BUT ... trying this out on the engine at my immediate disposal (OnLine 7.24.UC1) gives me a lot of syntax errors when trying to convert INTERVAL with a lot of parentheses. No about of TEMP TABLE creation and selecting in and out to try to make it work bore any fruit. So, if you still need to know, let me know: 1) What "second #0" is (anything before the start of the decade would be too far in the past to deal with in INTEGER or INTERVAL sense) 2) Language of choice (I may be able to get a stored procedure to work, although they are cumbersome). A 4GL or NewEra calculation is likely possible, the results of which could be stored in the database. Sounds like another weekend project!   <TT>//////////////// =======================================================</TT> <TT>//////////    // Dennis J. Pimple           Informix Software, Inc.</TT> <TT>//////    /  /// Principal Consultant       6300 S Syracuse Way Ste 205</TT> <TT>/////    // //// dennisp@informix.com       Englewood CO 80111</TT> <TT>////    // /////</TT> <TT>///    // ////// recept: 303-850-0210</TT> <TT>//    // /////// direct: 303-740-5611       Opinions expressed are mine,</TT> <TT>/    ///////////    fax: 303-779-4025       and do not necessarily</TT> <TT>////////////////   <A HREF="http://www.informix.com">http://www.informix.com</A>  reflect those of my employer</TT>  </HTML> --------------771BCA909ACD3BAEE14C085E--