List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 15 1999 6:12pm
Subject:Re: Saving dates as YYYYMMDD
View as plain text  
"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

Thread
Saving dates as YYYYMMDDClaudia M. Castaneda14 Sep
  • Re: Saving dates as YYYYMMDDMike Machado14 Sep
    • Re: Saving dates as YYYYMMDDMartin Ramsch14 Sep
  • Re: Saving dates as YYYYMMDDChristian Mack14 Sep
    • Re: Saving dates as YYYYMMDDDaniel E. White15 Sep
      • Re: Saving dates as YYYYMMDDMichael Widenius17 Sep
  • Re: Saving dates as YYYYMMDDChristian Mack15 Sep
    • Re: Saving dates as YYYYMMDDMartin Ramsch16 Sep
      • Re: Saving dates as YYYYMMDDMichael Widenius17 Sep