Re: How best to implement DATETIME?

Path: emory!swrinde!cs.utexas.edu!sun-barr!male.EBay.Sun.COM!jethro.Corp.Sun.COM!exodus.Eng.Sun.COM!appserv.Eng.Sun.COM!sun!amdcad!weitek!pyramid!infmx!news From: cortesi@informix.com (David Cortesi) Newsgroups: comp.databases.informix Message-ID: <1992Mar13.174649.27893@informix.com> Date: 13 Mar 92 17:46:49 GMT References: <62980007@col.hp.com> Sender: news@informix.com (Usenet News) Reply-To: cortesi@informix.com Organization: Informix Software, Inc. In article <62980007@col.hp.com> judym@col.hp.com (Judy Miller) writes: > How best to implement DATETIME? > > I'm new to Informix and do not have any experts of examples ... just the > manuals. I need to represent a date and time in an Informix database for > reporting purposes.... Well, if you are using 4.0 then you can find an appendix on "Using DATETIME and INTERVAL" in those manuals (which you really ought to consult *before* posting to the net, you know, tsk tsk tsk). If you are using 4.1, then look in your Informix Guide to SQL:Tutorial, Chapter 9, under "The Chronological Types." > 1. How would I define MY_TIME in the Informix database? DATETIME > 2. What format should the data be in when updating the column MY_TIME? UPDATE...SET MY_TIME = "1992-03-13 09:27:25.0" > 3. Do I need to do anything special on the ISQL Form to view the datetime? nope. Well, use EXTEND to select sub-fields, maybe. > 4. Do I need to do anything special in the ISQL Report to extract the datetime? nope. Well, SELECT EXTEND(...) to extract sub-fields, maybe. > 5. How do I specify in SQL "give me everything from today after 2:00 PM?" Ah! Now this is tricky. The way you say "2:00 PM TODAY" is, first you get the current time and trim off the hours and minutes: EXTEND(CURRENT,YEAR TO DAY) then you put back zero hours and minutes, EXTEND(EXTEND(CURRENT.YEAR TO DAY),YEAR TO MINUTE) then you add on the constant 14 hours to get to 2PM: EXTEND(EXTEND(CURRENT,YEAR TO DAY),YEAR TO MINUTE)+14 UNITS HOUR So: SELECT ... FROM ...WHERE MY_TIME >= EXTEND(EXTEND(CURRENT,YEAR TO DAY),YEAR TO MINUTE)+14 UNITS HOUR (all-cap keywords typed using the Paul Mahler memorial shift key...) > The database is being updated by a Monitrol program (C-like) which supplies > the number of seconds from 1970. (Is that a common UNIX practice, or just > Monitrol? Does Informix recognize this format?) No. > I don't have to use the Monitrol representation ... I have the ability to > use the UNIX date command to return the date and time in a more meaningful > format before updating the database. date '+%y-%m-%d %H:%M:%S' > But I'm not clear on how to implement DATETIME in Informix: Be sure and let us know of anything unclear or missing...mail to "doc@informix.com" is reviewed by the tech pubs managers!