UNIX timestamp issue on MYSQL insert via PHP

Question

Im receiving data with a created time field given in a unix timestamp format for example "1251324809" which to my understanding is the amount of time elapsed since the epoch, 1970.

Ive got a DATETIME formatted field in a MYSQL table, and im using this to return the UNIX time to a YY-MM-DD HH:MM:SS format:

date('Y-m-d H:i:s', $timestamp);

and then storing it in the database. The problem is some of my dates are being stored accurately and some of them are being stored as 1970-01-01 02:00:00, its only a small percentage of the data but i need to know why? Am i perhaps receiving the data incorrectly sometimes?

Pekka · Accepted Answer · 2010-10-18 23:37:30Z

1

Am i perhaps receiving the data incorrectly sometimes?

Probably. January 1st, 1970 points to $timestamp being zero or a value that can't be parsed as a time stamp.

answered Oct 18, 2010 at 23:37

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

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

3 Comments

Baadier Sydow Over a year ago

would it perhaps be more elegant if i used isset to test if there is a value and if not assign it the current time?

Pekka Over a year ago

@Baadier that entirely depends on your database - but I would rather insert 0000-00-00 00:00:00 into the database if the timestamp is zero

Baadier Sydow Over a year ago

id like to be able to work with the data that is pulled, and with the content being twitter powered i can assume that the data will at least be current even if the timestamp is errornous. So with the twitter API only allowing search for 7 days i can assume that the content cannot be older than 7 days and hence im relatively comfortable in assigning the current time in the case of an incorrect timestamp being received.

Zurtri · Accepted Answer · 2010-10-19 01:55:01Z

0

I find that dates in php and mysql never work properly. Particualrly as I am in a vastly different timezone than my server.

In mysql I avoid the problem by having the date fields stored as int(11)

I then insert the php calculated timestamp (usually via time()) into them.

answered Oct 19, 2010 at 1:55

Zurtri

112 bronze badges

Collectives™ on Stack Overflow

UNIX timestamp issue on MYSQL insert via PHP

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related