0

I have a variable in my database of type timestamp. Its default value is "CURRENT_TIMESTAMP" which is a great feature.

However when I output the variable in the template it is formatted to an actual date. Is php doing something fancy here or is the date in the database still stored as a timestamp? I need to perform some calculations on the date for some features in the system.

Improve this question
1
  • current_timestamp stores an actual date and time. Commented Aug 25, 2011 at 22:56

2 Answers 2

Reset to default
3

You are confusing MySQL TIMESTAMPs (YYYY-MM-DD HH:MM:SS) with Unix TIMESTAMPS (seconds since the epoch).

SELECT UNIX_TIMESTAMP(timestamp_column_name) FROM ... should give you what you want

MySQL Manual (UNIX_TIMESTAMP)

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

Comments

1

When you first insert the value, CURRENT_TIMESTAMP is interpreted and replaced with the value of the function NOW() in the moment. For timestamps mysql stores a 4 byte integer which represents the amount of seconds since unix epoch in your current timezone. You can still make calculations in mysql using date and time functions (eg. return other formats, etc). If you're interested you can make calculations with this value in PHP also.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html#id624727

http://en.wikipedia.org/wiki/Unix_time

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

http://us.php.net/manual/es/ref.datetime.php

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.