Professional Web Applications Themes

Best Method to insert a Date into a MySQL database? - Adobe Dreamweaver & Contribute

Dear all, I would like to know which is the best method to insert date into a field in a table of a MySQL database and be able in the future to handle the record according to that date. For example, I would have a variety of orders and I would like to have the orders of last month, or of last year. Could you possibly suggest the best way to use? Thank you in advance....

  1. #1

    Default Best Method to insert a Date into a MySQL database?

    Dear all,

    I would like to know which is the best method to insert date into a field in a
    table of a MySQL database and be able in the future to handle the record
    according to that date. For example, I would have a variety of orders and I
    would like to have the orders of last month, or of last year. Could you
    possibly suggest the best way to use? Thank you in advance.

    DigiWolf Guest

  2. #2

    Default Re: Best Method to insert a Date into a MySQL database?

    DigiWolf wrote:
    > I would like to know which is the best method to insert date into a field in a
    > table of a MySQL database
    There is only one way: YYYY-MM-DD.
    > according to that date. For example, I would have a variety of orders and I
    > would like to have the orders of last month, or of last year.
    To retrieve orders from a particular month, use the MySQL Date and Time
    functions.

    For example, to get last month's orders:

    SELECT * FROM orders
    WHERE MONTH(orderDate) = MONTH(NOW()) - 1

    [url]http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html[/url]

    --
    David Powers
    Author, "Foundation PHP 5 for Flash" (friends of ED)
    Co-author "PHP Web Development with DW MX 2004" (Apress)
    [url]http://computerbookshelf.com[/url]
    David Powers Guest

  3. #3

    Default Re: Best Method to insert a Date into a MySQL database?

    .oO(David Powers)
    >DigiWolf wrote:
    >> I would like to know which is the best method to insert date into a field in a
    >> table of a MySQL database
    >
    >There is only one way: YYYY-MM-DD.
    The format is handled by MySQL if you use the correct type (DATE or
    DATETIME).

    Micha
    Michael Fesser Guest

  4. #4

    Default Re: Best Method to insert a Date into a MySQL database?

    I'd disagree. I've always found that storing the date as a unix timestamp is
    the most flexible.
    Especially if you're using php and use the mktime function with php.
    I've noticed that many of the major open source applications use this method
    as well.





    Ross Riley Guest

  5. #5

    Default Re: Best Method to insert a Date into a MySQL database?

    .oO(Ross Riley)
    >I'd disagree. I've always found that storing the date as a unix timestamp is
    >the most flexible.
    I disagree. The Unix timestamp is restricted in range (usually 1970-
    2038, a bit larger on systems that can handle negative timestamps) and
    not MySQL's native format, you can't use MySQL's date and time functions
    with it. If you store dates in a database you should use the DB's native
    date type if available.
    > Especially if you're using php and use the mktime function with php.
    I would avoid Unix timestamps whenever possible. Other formats are much
    more flexible, libraries exist if necessary.

    Micha
    Michael Fesser Guest

  6. #6

    Default Re: Best Method to insert a Date into a MySQL database?

    Michael Fesser wrote:
    > .oO(David Powers)
    >>
    >>There is only one way: YYYY-MM-DD.
    >
    >
    > The format is handled by MySQL if you use the correct type (DATE or
    > DATETIME).
    It still has to be presented in the right order.

    YYYYMMDD will work, as will a number of other variations.

    MMDDYYYY will not.

    Experienced users can take the risk of presenting the date in a
    non-standard format. I believe it's better to use the native MySQL
    format (YYYY-MM-DD) whatever level of expertise you have. There's less
    danger of future versions failing to accept an unorthodox format.

    --
    David Powers
    Author, "Foundation PHP 5 for Flash" (friends of ED)
    Co-author "PHP Web Development with DW MX 2004" (Apress)
    [url]http://computerbookshelf.com[/url]
    David Powers Guest

  7. #7

    Default Re: Best Method to insert a Date into a MySQL database?

    Thank you all for your replies. I would like to know if there is the opportunity to use MySQL functions in order to enter date, time and GMT+2 time difference within a date field. Thank you again.
    DigiWolf Guest

  8. #8

    Default Best Method to insert a Date into a MySQL database?

    Here from all your discussion one point is not still clear to me. I am giving here an example. Somebody in a HTML/PHP form input day value in a text box as 29, month as 02, year as 1987.

    Day: 29
    Month: 02
    Year: 1987

    All values are strings, so after submittinh the PHP form you get values as POST variable. as below-
    After submit button click get the inputted values via PHP / HTML date form-
    $day = $_POST["day"];
    $mon = $_POST["month"];
    $yr = $_POST["year"]; // 1987

    So you build up a string : as $date = $yr.'-'.$mon.'-'.$day;

    And put this $date variable in MySQL table by a INSERT INTO table(date-field) values('$date');

    All correct ? or wrong ?

    Thanks
    Bik
    Rigseo Seo Optimization is offline Junior Member
    Join Date
    Dec 2011
    Posts
    1

Similar Threads

  1. Question How to insert xml data into mysql database using php ?
    By John in forum Brainstorming Area
    Replies: 0
    Last Post: August 22nd, 10:10 AM
  2. inserting date into MySql Database
    By khotwheels in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 2nd, 12:59 AM
  3. Inserting Current Date into MySQL Database
    By Gerard Shea in forum PHP Development
    Replies: 4
    Last Post: August 22nd, 10:00 PM
  4. inserting the current date into mysql database fild
    By Paul Brown in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 24th, 03:51 PM
  5. text to date and insert to mysql...
    By PhilM in forum PHP Development
    Replies: 1
    Last Post: July 11th, 04:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139