Ask a Question Register Login

Can someone please clarify how MySQL's TIMESTAMP is used in conjunction with PHP's DateTime class?

683

I've been studying the differences in usage between MySQL's DATETIME and TIMESTAMP. It seems that it's pretty straight forward with DATETIME. I would use the following procedure:

  1. Choose the default timezone for all dates, such as UTC.
  2. Let user select a date from drop-down.
  3. Create new PHP DateTime object with the chosen date, using the user's timezone settings, such as EST.
  4. Convert the object to UTC, and insert into database.
  5. On another page, retrieve datetime value and make a new DateTime object with it, using UTC timezone.
  6. Convert object to user's local time (EST), and display to him.

Now, it seems that mysql's TIMESTAMP column type can help eliminate all of these conversions.

  1. What would the above steps look like with the TIMESTAMP column type?
  2. Would I need to do "SET time_zone = timezone;" in the beginning of each pageload to set the timezone to the location of the user?
  3. Would it be easier to ONLY use one type of date column type per database? If not, it may require two different sets of functions to produce the right date.
  4. Should TIMESTAMP only be used in columns not intended to be shown to the public (so as not to deal with formatting)? Like when a row was created, last edited, etc.
630

Answer

Solution:

I have not tested any of this approach, but it seems pretty straightforward =)

  1. You shouldn't need to convert dates, just set the time zone when you read/write from dB to get everything right.
  2. Yes, you will have to set right time zone after connection to dB is made.
  3. You mean to only use datetime or timestamp? It really depends on how you intend to use the columns. But there isn't a clear have to do.
  4. Same as above, it isn't wrong formatting your data from the dB, with a timestamp you can return date style strings from the dB so no worries

Traditionally timestamp is associated like you mention, and datetime for other dates.

more on locale/time zone: MYSQL set timezone in PHP code

People are also looking for solutions to the problem: PHP and SimpleXML - Can't Output File Contents

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.