• 2
Votes
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 190

Backtrace:

File: /var/www/stackquestions.com/public_html/application/views/question.php
Line: 190
Function: _error_handler

File: /var/www/stackquestions.com/public_html/application/controllers/Questions.php
Line: 416
Function: view

File: /var/www/stackquestions.com/public_html/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

Should the datetime or timestamp data type be used in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

I'm working with PHP on the server side.

The below examples show how the TIMESTAMP date type changed the values after changing the time-zone to 'america/new_york' where DATETIMEis unchanged.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

I've converted my answer into article so more people can find this useful, MySQL: Datetime Versus Timestamp Data Types.

  • 0
Reply Report

In MySQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

  • 1
Reply Report

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

  • 3
Reply Report