how to use the datetime or timestamp data type in MySQL?

in this post i will give MySQL datetime field,how to use the datetime or timestamp data type in MySQL?. 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.

how to use the datetime or timestamp data type in MySQL?

Solution For: how to use the datetime or timestamp data type in MySQL?:

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 |
+---------------------+---------------------+

Special cases:

  • Date only — if you only care about the date (like the date of the next Lunar New Year, 2022-02-01) AND you have a clear understanding of what timezone that date applies (or don’t care, as in the case of Lunar New Year) then use the DATE column type.
  • Record insert times — if you are logging the insert dates/times for rows in your database AND you don’t care that your application will break in the next 17 years, then go ahead and use TIMESTAMP with a default value of CURRENT_TIMESTAMP().

How to use BIGINT?

Define:

CREATE TEMPORARY TABLE bad_times (
    b_time BIGINT
)

Insert a specific value ok:

INSERT INTO bad_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

Or of course this is much better from your application, like:

$statement = $myDB->prepare('INSERT INTO bad_times VALUES (?)');
$statement->execute([$someTime->getTimestamp()]);

Select:

SELECT b_time FROM bad_times;

i hope i will help you……

1 thought on “how to use the datetime or timestamp data type in MySQL?”

Leave a Comment