"Daniel E. White" wrote:
>
> At 8:45 PM +0200 9/14/99, Christian Mack wrote:
> >"Claudia M. Castaneda" wrote:
> >>
> >> Is it possible to have the DB save the date as YYYYMMDD and the time as
> >> HHMMSS using the date and time types respectively and not return a
> >> formated string, i.e "YYYY-MM-DD" or "HH-MM-SS" ?
> >>
> >> ---
> >> Claudia M. Castaneda
> >
> >Hi Claudia
> >
> >Thats easy :)
> >CREATE TABLE time_table (date_field DATE, time_field TIME, datetime_field
> DATETIME);
> >INSERT INTO time_table VALUES( NOW(), NOW(), NOW() );
> >SELECT date_field + 0, time_field + 0, datetime_field + 0 FROM time_table;
> >
>
> That is doing it a bit sideways, my friend.
> You want to avoid using the NOW() function to fill a TIME data-type.
> I found you need to use CURTIME() or CURRENT_TIME to get TIME_FORMAT to behave.
> Also, there are the DATE_FORMAT and TIME_FORMAT functions that allow for more
> flexibility.
>
> Observe:
> -------------------------
>
> mysql> CREATE TABLE time_table (date_field DATE, time_field TIME, datetime_field
> DATETIME );
> Query OK, 0 rows affected (0.20 sec)
>
> mysql> INSERT INTO time_table VALUES
> -> ( NOW(), CURTIME(), NOW()) ,
> -> ( NOW(), NOW(), NOW() );
> Query OK, 2 rows affected (0.03 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> select * from time_table ;
> +------------+------------+---------------------+
> | date_field | time_field | datetime_field |
> +------------+------------+---------------------+
> | 1999-09-14 | 20:49:01 | 1999-09-14 20:49:01 |
> | 1999-09-14 | 356:49:01 | 1999-09-14 20:49:01 |
> +------------+------------+---------------------+
> 2 rows in set (0.01 sec)
< ... >
> Gee. The things we learn when we experiment a bit.
>
> Hope I have been helpful.
>
> Anyone know what that 356 is ? It sure is NOT Julian day.
>
> I suspect that NOW() just should not be used for TIME datatype as it generates a full
> date-time value.
> --
> Dan White
Hi Dan
You are absolutely right.
I just wanted to create some simple values.
The 365 are houres!
The TIME field has a valid range from '-838:59:59' to '838:59:59' and it correctly uses
14 as days.
So it calculates 14 days + 20 h 49min 01 sec = 356 h 49 min 01 sec.
Months and years are skipped, because out of the valid TIME range.
Tschau
Christian
