CODEWITHSUNDEEP
mysqlphpmyadmin
user3766930
user3766930

Reputation: 5829

Why I can't store a timestamp in mysql / phpmyadmin?

I have a table created in phpMyAdmin and it contains two fields: start_time and text_modified. It looks like this

http://i.imgur.com/fHcWyOh.png

so the start_time might be null. When I'm filling the data in phpmyadmin I can choose the date and time that should be represented as this timestamp:

http://i.imgur.com/XvpjuPS.png

After doing so I expect to store a timestamp value in this field instead of date time. But when I do a query SELECT start_time from table I see there this:

http://i.imgur.com/Nwam2e1.png

So I assumed that it is just the php my admin that shows me automatically all dates as a date time value instead of timestamps. But now when I do a query: SELECT FROM_UNIXTIME(start_time) FROM table I'm getting those results:

http://i.imgur.com/gOZdoU3.png

and instead I want normal dates here. What is going wrong here?

Upvotes: 2

Views: 979

Answers (1)

fancyPants
fancyPants

Reputation: 51888

In a timestamp you can insert datetime values, that are internally stored as integers (the seconds since 1970-01-01 as you probably know). When you select them, they are displayed as date and time.

So far so good.

When you have values like 0000-00-00 00:00:00 you probably inserted NULL values or invalid dates or dates out of range for the integer value. Using FROM_UNIXTIME() doesn't make sense here, since this function calculates a date and time value from an integer value. This integer value of the timestamp column is like I said only used internally. Therefore you get NULL values for valid dates and 1970-01-01 for invalid dates since those were presumably treated as 0 and 0 seconds since 1970-01-01 00:00:00 is, surprise, 1970-01-01 00:00:00.

Upvotes: 2

Related Questions