Displaying queried date field issue using PHP, CodeIgniter, and MySQL?

Question

I altered a table in MySQL to be a timestamp so I could see when a user was created:

ALTER TABLE  `users` CHANGE  `created_date`  `created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

Using CodeIgniter I queried the database and got my row results for all users:

$this->db->get('users');

In my view I'm trying to display the results using PHP date():

<?php echo date("d-m-Y", $row->created_date); ?>

and I'm getting this error message, which I don't understand:

Severity: Notice
Message: A non well formed numeric value encountered
Filename: users/view_all_users.php
Line Number: 19

where by itself:

<?php echo $row->created_date ?>

produces:

01-01-1970 0000-00-00 00:00:00

Any ideas what I've done wrong?

Félix Adriyel Gagnon-Grenier · Accepted Answer · 2014-07-25 18:13:04Z

3

the second argument for date() must be a valid time stamp. 01-01-1970 0000-00-00 00:00:00 is not a valid timestamp.

$arr = explode(' ',$row->created_date);
$date = $arr[1] . ' ' . $arr[2];
echo date("d-m-Y", strtotime($date));

note that this string: 01-01-1970 0000-00-00 00:00:00 probably denotes that the value in your field is wrong, but this string: 1970-01-01 00:00:00 is valid for use with strtotime()

edit I have an eerie feeling though, are you sure that the full string is outputed by the second echo, or are your two echos taking place, hence mimicking an invalid string? in which case this:

echo date("d-m-Y", strtotime($row->created_date));

will work right away!

edited Jul 25, 2014 at 18:13

answered Jul 25, 2014 at 17:40

Félix Adriyel Gagnon-Grenier

8,94510 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mtpultz Over a year ago

How would I alter my table to get a valid time stamp, instead of handling it code? Also not sure why it's giving me 1970 date, that's not a current timestamp from my system.

Félix Adriyel Gagnon-Grenier Over a year ago

why do you need a timestamp instead of a easy to use/format/think about DATETIME field? also getting a valid timestamp in your field is more about putting a valid timestamp in then altering your table

mtpultz Over a year ago

I can definitely use a date field, but I couldn't figure out how to add it in MyPHPAdmin and have it add a current date default. Only option was CURRENT_TIMESTAMP.

Félix Adriyel Gagnon-Grenier Over a year ago

can you post the code which defines the row? the string returned is not a timestamp, it must be changed somehow by codeigniter

mtpultz Over a year ago

I also changed my ALTER TABLE SQL by added DATETIME instead of TIMESTAMP, so it looks like this now ALTER TABLE users CHANGE created_date created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; So it should be a date time now, and without using the PHP date() it shows the current date and time as 2014-07-25 10:59:25, so I think you're right CodeIgniter is handling the date field.

|

Jerry · Accepted Answer · 2014-07-25 17:44:31Z

0

MySQL returns that cumbersome string when you use the TIMESTAMP data type. You can either convert it to the Unix value you are expecting in your query:

SELECT UNIX_TIMESTAMP(`created_date`)...

(and also convert your timestamp to a string when you save it)

Or you can simply use an integer data type in the database and bypass all the string business.

answered Jul 25, 2014 at 17:44

Jerry

3,6181 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

Displaying queried date field issue using PHP, CodeIgniter, and MySQL?

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related