1

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:

$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.

2

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.

  • There is a typo in your code. You created end_date but are inserting data into end-date. I am marking this for closing as 'too localized' but you could just delete it. – s_ha_dum Apr 24 '13 at 20:51
  • OMG! Thanks so much! That was it and yes, I will delete it. – clockwiseq Apr 24 '13 at 20:53
  • apparently I don't have the cred enough to delete it, so you can close it. – clockwiseq Apr 24 '13 at 21:00
  • for others looking at this, there's no need to make strtotime of 2012-08-14 and then generate the same again with date - just 'begin_date' => '2012-08-14' is fine – Alex K Jul 7 '16 at 16:21
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.