mysql date issues

Discussion in 'Web Design and Development' started by Cabbit, Jun 16, 2008.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    why is the default date format 0000-00-00 and why cant i change it to 00-00-0000
     
  2. notnek macrumors 6502

    notnek

    Joined:
    Oct 25, 2007
    #2
    i always just have a row varchar(10) and whenever im running some function i have date('m.d.Y'); inserted into that row. creates 06.16.2008
     
  3. Knox Administrator

    Knox

    Staff Member

    Joined:
    Jul 1, 2002
    Location:
    UK
    #3
    yyyy-mm-dd is the international standard date format, which has the advantage of not being ambiguous like 00-00-0000 (i.e. is it mm-dd-yyyy or dd-mm-yyyy?). Within MySQL statements you can use the str_to_date and date_format functions to change other formats into the format MySQL wants and vice versa. Or, you can do the transformation within the programming language - strtotime() and date() in PHP for example.

    The problem with storing dates as strings is that you can't (i think) use MySQL's built in functions for manipulating dates, and it won't be as efficient.
     
  4. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #4
    thanks for the help, unfortunately i cant store as a varchar as it makes sorting them ascending and descending impossible so i will just have to leave em at the current format.
     
  5. Luveno macrumors member

    Luveno

    Joined:
    May 12, 2006
    Location:
    Nova Scotia, Canada
    #5
    You can always alter them to whichever format you'd like when you're pulling them back out of the database pretty easily, if the format is a hassle.
     
  6. nomade macrumors member

    nomade

    Joined:
    Dec 2, 2006
    #6
    $date_format=explode('-',$line['date']);
    echo "".$date_format[2]."-".$date_format[1]."-".$date_format[0]."";
     
  7. HomeBru Studios macrumors member

    HomeBru Studios

    Joined:
    Jun 4, 2008
    #7
    If you are a programmer, there is actually a good reason for it! It is known as Japanese format and if the date was in a text string, it would be immediately sort-able without having to juxtapose the date's components first.

     
  8. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #8
    Some informal thoughts, a bit of a sidebar discussion...

    Traditionally I use the "datetime" type, which is 0000-00-00 00:00:00 and parse the time/date as I need it via code. You can also sort on this field and its useful to calendar driven apps or apps that use form fields to search by year, day, month, hour min and second (plugging in variables into a query). This method is also quite useful when browsing the inserted records via phpMyAdmin or some external utility like Toad, etc., because the datetime fields are human readable. Plus, MySQL has alot of date and time functions that work very well with this field without needing to do any additional conversion.

    Or...

    If you don't need to browse the database manually nor do you wish to use the advanced date/time functions built into Myself then one of the most efficient and SIMPLE methods for storing and sorting based date and time is to define a field as int(11) , name it timestamp, and store the Unix timestamp. Then you can use from_unixtime() and unix_timestamp() functions to convert to/from that format. And obviously sorting is efficient, even if you define the type as varchr(11). It still works.

    The OP wants to store only the date, I follow that, but both these procedures are efficient, sort well, and happen to include the time which might be useful to the developer anyway for history logging.

    Just thinkin' out loud, the previous advice was excellent.

    -jim
     
  9. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #9
    thanks for the replys folks got it the way i like, still confuseing why it is as it is but hey it looks like i wana i dont mind how sql sees it
     

Share This Page