Issues with timestamp field values

Question

I have got some issues with timestamp field values.

I'm using Codeigniter 2.1.3, with PHP 5. Issue is that when tries to enter a empty(or NULL) data using $this->db->insert(), DB shows the value as 0000-00-00 00:00:00.

<?php
$activities = array(
    'activity_title'    => $task['title'],
    'note'              => $task['description'],
    'duration'          => "00:15",
    'created_date'      => mysql_datetime(),
    'scheduled_date'    => $task['schedule_time'], // DB field type: 'timestamp' with 'NULL' as default
    'owner_id'          => $owner_id,
    'assigned_to'       => $assigned_to,
);

if (($assigned_to != '') && (isset($task['schedule_time'])) && ($task['schedule_time'] != '')) {
    $userdtl = _DB_get_record($this->tables['users'], array('id' => $assigned_to));
    date_default_timezone_set('UTC');
    $originalTime = new DateTime($task['schedule_time']);
    //  Convert to User TimeZone.
    $originalTime->setTimeZone(new DateTimeZone($userdtl['timezone']));
    $activities['scheduled_date'] = $originalTime->format('Y-m-d H:i:s');
}

if (($activities['scheduled_date'] == '0000-00-00 00:00:00' || $activities['scheduled_date'] == '' || $activities['scheduled_date'] == 'NULL')) {
    $activities['scheduled_date'] = NULL;
    file_put_contents('./log.txt', print_r($activities['scheduled_date'], true), FILE_APPEND); // getting empty
}

// Insertion
$this->db->insert($this->tables['activities'], $activities);

// On print_r($this->db->last_query());
//INSERT INTO `activities` (`activity_title``, `note`, `duration`, `created_date`, `scheduled_date`, `owner_id`, `assigned_to`) VALUES ('Email 3', '<div>Email 3</div>', '00:15', '2017-07-21 05:55:52', NULL, '633', '633')

?>

When I check DB for the value, it show 0000-00-00 00:00:00. It should be NULL, right? I have directly checked same insertion query on DB & it worked fine.

I do not know more about server details & its workin on UTC timezone.

The DB column as:

INDEX as:

Do you have any idea on this? Please help...

Before `Down vote`, please say why you are doing that? Put a helping hand. — Sinto, Jul 21 '17 at 08:00
Have you had a look at [this previous question](https://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql) ? It seems like it could help. — roberto06, Jul 21 '17 at 08:01
Ty, for that. I have checked it but in my code I have set that to `NULL`. Sorry the field type is `TIMESTAMP` — Sinto, Jul 21 '17 at 08:07
@Sinto what does `DESC activities` print for `scheuled_date` column? Can you add that bit in the question? — Darshan Mehta, Jul 21 '17 at 08:19
@DarshanMehta: Sorry, I do not get you. Sort `DESC` on `scheuled_date`? — Sinto, Jul 21 '17 at 09:20
I want to know the type, default value and constraints present on `scheduled_date` column — Darshan Mehta, Jul 21 '17 at 09:29
Field Type: `TIMESTAMP`, Default: `NULL` & the field is `INDEXED`. I have added the indexed details now. — Sinto, Jul 21 '17 at 09:34
pretty much amusing seeing guys downvoting a question like this ... sinto describes the problem pretty clear - so why do you guys downvote this ? — Atural, Jul 21 '17 at 09:40
@sintakonte: Ty man for support. Asking this why because I have search & checked my code for 2 day. `Down voting` is a pretty easy task than a small help. — Sinto, Jul 21 '17 at 09:47

Atural · Answer 1 · 2017-07-21T09:38:32.117

0

if your db column is default NULL you can try to avoid the insert at all

try the following

if (($activities['scheduled_date'] == '0000-00-00 00:00:00' || $activities['scheduled_date'] == '' || $activities['scheduled_date'] == 'NULL')) {
    unset($activities['scheduled_date']);
    file_put_contents('./log.txt', print_r($activities['scheduled_date'], true), FILE_APPEND); // getting empty
}

this should pretty much do the job

Maybe the problem is here because CI protects your query by escaping the proper values (although i'm not sure)

//edit - after your comment Try to output the insert statement because i think there is a problem somewhere else

Try this and show us your Statement please

$this->db->insert($this->tables['activities'], $activities);
$strSql = $this->db->last_query();
echo $strSql;

edited Jul 21 '17 at 09:38

answered Jul 21 '17 at 09:07

Atural

5,389
5
18
35

You mean after `unset` the field print the `$this->db->last_query()` ? I double that this issue is because of `INDEXING`, Im not sure on it. Because I have same codes on two different servers(one for development & other for Live). In Development is working fine & there its not indexed. – Sinto Jul 21 '17 at 09:49
I have already added that in my Question as: `INSERT INTO `activities` (`activity_title``, `note`, `duration`, `created_date`, `scheduled_date`, `owner_id`, `assigned_to`) VALUES ('Email 3', '
Email 3
', '00:15', '2017-07-21 05:55:52', NULL, '633', '633')` – Sinto Jul 21 '17 at 09:52
this is generated by Codeigniter? – Atural Jul 21 '17 at 09:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/149795/discussion-between-sinto-and-sintakonte). – Sinto Jul 21 '17 at 09:54

Issues with timestamp field values

1 Answers1