Re: Converting Integer into a Date / Time and Calculating Elapsed time

Jonathan Leffler wrote: > QueriX 4GL / Mehdi wrote: > > > og wrote: > > > I have an Informix Database that has a Creation Date of a Help Desk Ticket > > > in an integer format that represents the number of seconds from 01/01/1970. > > > It is stored in an integer field . I'm trying to write a 4gl report to: > > > > > > 1. Calculate and display the integer into a date / time format. > > > 2. Calculate the elapsed time in day / hour / seconds from the date of > > > creation to the current date / time. > > > > > > Has anybody already developed the algorithm and procedure to do this? > > > > DEFINE start_date YEAR TO SECOND > > DEFINE tickect_date YEAR TO SECOND > > Two syntax errors: use DATETIME YEAR TO SECOND > > > DEFINE ticket_time INTEGER > > DEFINE elapsed_time INTERVAL YEAR TO SECOND > > Syntax error: > > DEFINE elapsed_time INTERVAL DAY(9) TO SECOND > > > LET start_date ="01-01-1970 00:00:00" > > Runtime error: > > LET start_date = "1970-01-01 00:00:00" > > > LET ticket_date = start_date + ticket_time UNIT SECONDS > > Syntax error: > > LET ticket_date = start_date + ticket_time UNITS SECOND > > > LET elapsed_time= CURRENT - ticket_date > > Otherwise correct. > > Oh, and you can control this in expressions if you're very careful: > > SELECT INTERVAL(0 0:0:0) DAY(9) TO SECOND + (CURRENT YEAR TO SECOND - > (DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + > ticket_time UNITS SECOND)) > FROM ... > > Now, I'll have to confess that I've not fed that expression through a > database, so there's a moderate chance I've made a syntax error or two. > The gist of what I'm saying should be clear, just as Mehdi's answer was > clear on the principles. > > Compliments of Informix. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN > #include <disclaimer.h> Oops I should have passed the syntax through the compiler ;-) thanks for correcting the syntax. -- Compliments of QueriX -------------------------------------------------------------------------------------------------- QueriX 4GL Compilers are Informix 4GL Compatible, and Connection to other RDBMS such as Oracle. Hydra 4GL Compiler (Compatible with I4GL) Compile once, run everywhere Phoenix Windows GUI. (Front End to 4GL) Chimera Java GUI The only GUI you will ever need... (Front End to 4GL) Arachne Web Technology (Front End to 4GL on the Web & HTML Report Generator) ESQL/C Compiler For more details visit: http://www.querix.com/ ---------------------------------------------------------------------------------------------------