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
