Thanks a bunch. I knew there must be an elegant and simple solution. I bet I could apply this to a few nasty recursive relationships too.
AJ ONeal On Mon, Sep 22, 2008 at 10:11 AM, Alberto Treviño <[EMAIL PROTECTED]> wrote: > On Monday 22 September 2008 09:43:12 am Alvin ONeal wrote: > > Let's say I have a table in a database that stores the exact time and > > each time that I do a query on that table I want it to retrieve that time > > in three distinct formats - one as unix time, one as mm/dd/yyyy and one > > as hh:mi:ss am. > > > > How can I create a view of the table so that SELECT * FROM PunchCardView > > will return those values? > > Well, this is the query I used to get your data on one of my databases: > > SELECT UNIX_TIMESTAMP(LastModified) AS UnixTimeStamp, > DATE_FORMAT(LastModified, '%m/%d/%Y') AS Date, > DATE_FORMAT(LastModified, '%H:%i:%s %p') AS Time > FROM Test > > To make a view you would just do: > > CREATE VIEW PunchCardView AS > SELECT UNIX_TIMESTAMP(LastModified) AS UnixTimeStamp, > DATE_FORMAT(LastModified, '%m/%d/%Y') AS Date, > DATE_FORMAT(LastModified, '%H:%i:%s %p') AS Time > FROM Test > > Running that view with "SELECT * FROM PunchCardView LIMIT 10" gave me: > > +---------------+------------+-------------+ > | UnixTimeStamp | Date | Time | > +---------------+------------+-------------+ > | 1133705076 | 12/04/2005 | 07:04:36 AM | > | 1162273173 | 10/30/2006 | 22:39:33 PM | > | 1143178485 | 03/23/2006 | 22:34:45 PM | > | 1133705076 | 12/04/2005 | 07:04:36 AM | > | 1179807598 | 05/21/2007 | 22:19:58 PM | > | 1203139014 | 02/15/2008 | 22:16:54 PM | > | 1133705076 | 12/04/2005 | 07:04:36 AM | > | 1161883692 | 10/26/2006 | 11:28:12 AM | > | 1133705076 | 12/04/2005 | 07:04:36 AM | > | 1133705076 | 12/04/2005 | 07:04:36 AM | > +---------------+------------+-------------+ > > Is that what you want? > > -- > Alberto Treviño > BYU Testing Center > Brigham Young University > > -------------------- > BYU Unix Users Group > http://uug.byu.edu/ > > The opinions expressed in this message are the responsibility of their > author. They are not endorsed by BYU, the BYU CS Department or BYU-UUG. > ___________________________________________________________________ > List Info: http://uug.byu.edu/mailman/listinfo/uug-list >
-------------------- BYU Unix Users Group http://uug.byu.edu/ The opinions expressed in this message are the responsibility of their author. They are not endorsed by BYU, the BYU CS Department or BYU-UUG. ___________________________________________________________________ List Info: http://uug.byu.edu/mailman/listinfo/uug-list
