Professional Web Applications Themes

problem with setting up a default value for a datetime variable - MySQL

OK, I am using MySQL Administrator 1.1.9 and MySQL 5.0.19 on Windows XP Pro. I am setting up a table which I will routinely populate by uploading the data from a CSV text file. What I will actually do is load the data into memory tables, and do my processing using these. And before I process the new data (which will come in daily), I intend to use the form of the INSERT statement that is designed for quickly copying data from one table to another. The destination tables are archives, ultimately to be used for queries supporting historical questions ...

  1. #1

    Default problem with setting up a default value for a datetime variable

    OK, I am using MySQL Administrator 1.1.9 and MySQL 5.0.19 on Windows XP
    Pro.

    I am setting up a table which I will routinely populate by uploading
    the data from a CSV text file. What I will actually do is load the
    data into memory tables, and do my processing using these. And before
    I process the new data (which will come in daily), I intend to use the
    form of the INSERT statement that is designed for quickly copying data
    from one table to another. The destination tables are archives,
    ultimately to be used for queries supporting historical questions or
    yses, and these, unlike the originals, need time stamps so we know
    the date and time at which the data were retrieved and processed.

    So, I have a couple extra columns relative to what is in the file, both
    intended to have a default value. The first is an integer to be used
    as an index, and it s to be autoincremented. That I have set up
    without difficulty.

    The column that is giving trouble is a date/time stamp, and it ought to
    have a default value of NOW(). I have tried using NOW() as a default
    value, as well as CURRENT_TIMESTAMP, and in both cases I get an error
    claiming that these values are invalid default values. According to my
    references dealing with SQL, as well as both the manual and a book I
    have on MySQL, I should be able to use either NOW() or
    CURRENT_TIMESTAMP as a default for a date or time variable. So what is
    wrong?

    Any ideas on what I can do to use ANSI standard SQL to set up and use
    my time variable with a default value of NOW()?

    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Re: problem with setting up a default value for a datetime variable

    Ted wrote:
    > Any ideas on what I can do to use ANSI standard SQL to set up and use
    > my time variable with a default value of NOW()?
    MySQL seems to support DEFAULT CURRENT_TIMESTAMP as a special behavior
    of the TIMESTAMP datatype only. This behavior doesn't work with the
    DATETIME datatype, nor does it work with DATE, TIME, or YEAR datatypes.

    As far as standard SQL solutions, I'd recommend either of the following:

    - Specify the value when you copy data from the origin table:

    INSERT INTO real_table (a, b, c, d)
    SELECT a, b, c, CURRENT_TIMESTAMP()
    FROM in_memory_table;

    - Write a BEFORE INSERT trigger on your table and write logic to
    substitute the value returned by CURRENT_TIMESTAMP() when the datetime
    column has a NULL state.

    DELIMITER |
    CREATE TRIGGER setdefault_ts BEFORE INSERT ON real_table
    FOR EACH ROW BEGIN
    SET NEW.d = COALESCE(NEW.d, CURRENT_TIMESTAMP());
    END

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Need help setting a default variable
    By Married2theMachine in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: August 18th, 07:49 PM
  2. DateTime - default Timezone ?
    By Kaare Rasmussen in forum PERL Modules
    Replies: 0
    Last Post: August 20th, 11:21 AM
  3. mySQL datetime field default now() fails
    By Phil Powell in forum PHP Programming
    Replies: 2
    Last Post: September 8th, 06:09 PM
  4. DataSet with default value for DateTime
    By Andres Aguiar in forum ASP.NET Web Services
    Replies: 0
    Last Post: August 12th, 03:59 AM
  5. datetime class: setting day, month...
    By Ante Perkovic in forum ASP.NET General
    Replies: 6
    Last Post: July 2nd, 11:34 AM

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