How-To: wpdb Insert Record With Date

1 Views

I am writing a plugin where I need to pre-load some data in the custom tables that I create upon Activation. My inserts have run fine thus far until I attempt to insert a record with a DATE datatype. Can someone please tell me what I have wrong here?

Database Table Definition:

Read More
$sql =  "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix.SSM_ACADEMIC_YEAR_TABLE. " (
                  id mediumint(12) NOT NULL AUTO_INCREMENT,
                  academicyear VARCHAR(200) NOT NULL,
                  begin_date date NOT NULL,
                  end_date date NOT NULL,
                  created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id));";

    $wpdb->query($sql);

Database Insert: 

$current_year = array(
            'academicyear' => '2012 - 2013',
            'begin_date'   => date('Y-m-d', '2012-08-14'),
            'end-date'     => date('Y-m-d', '2013-05-31')
        );

        $wpdb->insert($wpdb->prefix.SSM_ACADEMIC_YEAR_TABLE, $current_year);

I have tried many different methods like just using => ‘2012-08-14’ but that doesn’t work either. I’m sure there’s some syntax I am either missing or I have wrong.

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. date does not take a human readable string. It takes a Unix timestamp. You need to convert that human date to a timestamp with strtotime.

    'begin_date'   => date('Y-m-d', strtotime('2012-08-14')),

    Of course, there is really no need to convert a YEAR-MONTH-DAY date into a timestamp only to convert it back that a YEAR-MONTH-DAY format.

  2. You can just use the function current_time. For example:

    <?php $time = current_time( 'mysql' ); ?>

    The function will return the date-time string that mysql requires.

    You can find more details here. And if you are interested in “time zone programming problems”, you would like to read this article Managing times & dates in WordPress.

  3. The accepted answer is not correct. You can pass string date formats to database, as long as they are formated in a manner that database understands (e.g. 2022-01-15T20:34:34 or 2022-01-15 20:34:34). You can find mariadb specifications here, I am pretty sure they are the same as for MySQL.