MySQL date data type, time type usage summary

MySQL Date type: Date format, storage space occupied, date range comparison. Date type storage date format date range -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- a datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038 date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31 year 1 bytes YYYY 1901 ~ 2155

When creating a table in MySQL, it is easy to select the right data type for yourself against the table above. But whether to choose datetime or timestamp can be a bit tricky. Both datetime types have advantages: datetime has a relatively large date range; timestamp takes up only half the storage space of datetime.

In addition, columns of type timestamp have another feature: By default, the timestamp column is automatically filled/updated with the current time (CURRENT_TIMESTAMP) during insert, update data. "Automatic" means that if you leave it alone, MySQL will do it for you.

The code of the table is:

create table t8 (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` datetime default NULL
);

In general, I prefer to use the datetime date type.

Comparison between the two:

1. timestamp easily supports a smaller range than timedate. And it is easy to exceed the situation

2.timestamp is affected by timezone, MYSQL version and SQL MODE of the server.

MySQL Time type: Time format, storage space occupied, and time range. Time type storage time format range -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59

It's kind of weird that there's such a wide range of time ranges, especially since time can be negative. Later, I read the MySQL manual to know that this is designed to meet the two date and time subtraction.

select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59
select timediff('23:59:59', '12:00:00');                        -- 11:59:59

Note that the two parameters of timediff can only be of type datetime/timestamp, time, and the two parameters must be of the same type. datetime/timestamp is compared with datetime/timestamp. time is compared with time.

Although MySQL datetime types are relatively rich, but unfortunately, at present (2008-08-08) these datetime types can only support to the second level, not support milliseconds, microseconds. Nor does it produce a millisecond function.

MySQL: MySQL Date Data Type, MySQL Time Type Usage Summary applies to MySQL 5.X and later versions.

1.1 Get the current date + time function (date + time) : now()

mysql> select now();

+---------------------+
| now()               |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

In addition to the now() function, which gets the current date and time, MySQL also has the following functions:

current_timestamp(),current_timestamp,localtime(),localtime,localtimestamp -- (v4.0.6),localtimestamp() -- (v4.0.6)

These date and time functions are all equivalent to now(). Since the now() function is short and easy to remember, it is recommended to always use now() instead of the functions listed above.

1.2 Get the current date + time function: sysdate()

The sysdate() date-time function is similar to now(), except that now() gets the value at the start of execution, and sysdate() gets the value dynamically when the function is executed. Take a look at the following example:

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+ | now() | sleep(3) | now() | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2008-08-08 22:28:21 | | 0 2008-08-08 22:28:21 | +---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2008-08-08 22:28:41 | | 0 2008-08-08 22:28:44 | +---------------------+----------+---------------------+

As you can see, although the sleep is 3 seconds in the middle, the time value of the now() function is the same twice; The sysdate() function gives a difference of 3 seconds between the two times. sysdate() is described in the MySQL Manual as follows: Return the time at which the function executes.

sysdate() date-time function, rarely used in general.

 

2. Get the current date function: curdate()

mysql> select curdate();

+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+

The following two date functions are equivalent to curdate() :

current_date()
,current_date

3. Get the current time function: curtime()

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+

The following two time functions are equivalent to curtime() :

current_time()
,current_time

4. Get the current UTC date-time functions: utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+ | utc_timestamp() | utc_date() | utc_time() | now() | +---------------------+------------+------------+---------------------+ | 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 22:47:11 | 2008-08-08 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +

Because China is located in the Eastern 8 time zone, local time = UTC time + 8 hours. UTC time is very useful when the business involves multiple countries and regions.

 

 

MySQL Timestamp function 1. MySQL obtains the current timestamp function: current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+ | current_timestamp | current_timestamp() | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 23:22:24 2008-08-09 | 2008-08-09 23:22:24 | +---------------------+---------------------+

2. MySQL (Unix timestamp, date) conversion function:

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

Here's an example:

select unix_timestamp(); -- 1218290027 === Gets the UNIX time value of the current time

 

Convert a specific time to timestamp

select unix_timestamp('2008-08-08');           -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

Convert timestamp to time select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

 

 

 

3. MySQL timestamp conversion, increment and subtraction functions:

timestamp(date)                                     -- date to timestamp
timestamp(dt,time)                                  -- dt + time
timestampadd(unit,interval,datetime_expr)           --
timestampdiff(unit,datetime_expr1,datetime_expr2)   --

Look at the example section:

select timestamp('2008-08-08');                         -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01');    -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00');     -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

The MySQL timestampadd() function is similar to date_add().

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1 select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485 select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- 12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00');           -- 7

The MySQL timestampdiff() function is much more powerful than datediff(), which can only count the number of days between two dates.

 

 

 

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

 

 

2. MySQL date and time Extract function. 1. Select each part of the datetime: date, time, year, quarter, month, day, hour, minute, second, microsecond

set@dt = '2008-09-10 07:15:30.123456';

select date(@dt);        -- 2008-09-10
select time(@dt);        -- 07:15:30.123456
select year(@dt);        -- 2008
select quarter(@dt);     -- 3
select month(@dt);       -- 9
select week(@dt);        -- 36
select day(@dt);         -- 10
select hour(@dt);        -- 7
select minute(@dt);      -- 15
select second(@dt);      -- 30
select microsecond(@dt); -- 123456

2. The MySQL Extract() function can achieve a similar function above:

set@dt = '2008-09-10 07:15:30.123456';

select extract(year                from @dt); -- 2008
select extract(quarter             from @dt); -- 3
select extract(month               from @dt); -- 9
select extract(week                from @dt); -- 36
select extract(day                 from @dt); -- 10
select extract(hour                from @dt); -- 7
select extract(minute              from @dt); -- 15
select extract(second              from @dt); -- 30
select extract(microsecond         from @dt); -- 123456

select extract(year_month          from @dt); -- 200809
select extract(day_hour            from @dt); -- 1007
select extract(day_minute          from @dt); -- 100715
select extract(day_second          from @dt); -- 10071530
select extract(day_microsecond     from @dt); -- 10071530123456
select extract(hour_minute         from @dt); --    715
select extract(hour_second         from @dt); --    71530
select extract(hour_microsecond    from @dt); --    71530123456
select extract(minute_second       from @dt); --      1530
select extract(minute_microsecond from @dt); --      1530123456
select extract(second_microsecond from @dt); --        30123456

The MySQL Extract() function should have all its functions except date() and time(). It also has the function of selecting 'day_microsecond'. Notice that instead of just selecting day and microsecond, you select from the day portion of the date all the way to the microsecond portion. That's tough enough!

The only downside to the MySQL Extract() function is that you need to type a few more times.

3. MySQL dayof... Functions: dayofweek(), dayofmonth(), dayofyear()

Returns the position of the date parameter in the week, month, and year, respectively.

set @dt = '2008-08-08';

select dayofweek(@dt);   -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt);   -- 221

The date '2008-08-08' is the 6th day of the week (1 = Sunday, 2 = Monday,... , 7 = Saturday); The eighth day of January; The 221st day of the year.

4. MySQL week... Functions: week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';

select week(@dt);        -- 31
select week(@dt,3);      -- 32
select weekofyear(@dt); -- 32

select dayofweek(@dt);   -- 6
select weekday(@dt);     -- 4

select yearweek(@dt);    -- 200831

The MySQL week() function can have two parameters, see the manual for details. weekofyear(), like week(), calculates the weekof the year in which "day" is located. weekofyear(@dt) is equivalent to week(@dt,3).

The MySQL weekday() function is similar to dayofweek() in that it returns the position of "day" in the week. The difference lies in the reference standard, weekday: (0 = Monday, 1 = Tuesday,... , 6 = Sunday); dayofweek: (1 = Sunday, 2 = Monday,... , 7 = Saturday)

The MySQL yearweek() function returns the year(2008) + week location (31).

5. MySQL returns the week and monthname functions: dayname(), monthname()

set @dt = '2008-08-08';

select dayname(@dt);     -- Friday
select monthname(@dt);   -- August

Thinking, how to return the Chinese name?

6. MySQL last_day() function: Returns the last day of the month.

select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31

The MySQL last_day() function is very useful. For example, if I want to get the number of days in the current month, I can calculate it like this:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now()               | days |
+---------------------+------+
| 2008-08-09 11:45:45 |   31 |
+---------------------+------+

1. MySQL adds a time interval to the date: date_add()

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day select date_add(@dt, interval 1 hour); -- add 1 hour select date_add(@dt, interval 1 minute); -... select date_add(@dt, interval 1 second); select date_add(@dt, interval 1 microsecond); select date_add(@dt, interval 1 week); select date_add(@dt, interval 1 month); select date_add(@dt, interval 1 quarter); select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day);       -- sub 1 day

The MySQL adddate() and addtime() functions can be replaced with date_add(). Here is an example of date_add() implementing addtime() :

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03                            |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03                             |
+-------------------------------------------------+

The date_add() function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @dt, respectively. Suggestion: Always use the date_add() date-time function instead of adddate(), addtime().

2. MySQL subtracts a time interval from the date: date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+ | date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 1997-12-30 22:58:59 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

The MySQL date_sub() date-time function is used in the same way as date_add(). In addition, there are two functions in MySQL subdate(), subtime(), it is recommended that date_sub() be used instead.

3. MySQL alternative date functions: period_add(P,N), period_diff(P1,P2)

The format of the function argument "P" is "YYYYMM" or "YYMM", and the second argument "N" means increase or subtract N month.

MySQL period_add(P,N) : Add/subtract N months from the date.

mysql> select period_add(200808,2), period_add(20080808,-2)

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | period_add (200808, 2) | period_add | (20080808-2) + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 200810 | 20080806 +----------------------+-------------------------+

MySQL period_diff(P1,P2) : dates p1-p2, return N months.

mysql> select period_diff(200808, 200801);

+-----------------------------+ | period_diff(200808, 200801) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 7 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

In MySQL, these two date functions are rarely used in general.

4. MySQL date and time subtracting function: datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2) : Subtract two dates from date1-date2 to return the number of days.

select datediff('2008-08-08', '2008-08-01'); -- 7 select datediff('2008-08-01', '2008-08-08'); -- -- 7

MySQL timediff(time1,time2) : Two dates subtract time1-time2, returning the difference in time.

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00');                       -- 08:08:08

Note: Both arguments of the timediff(time1,time2) function must be of the same type.

1. MySQL (time, seconds) conversion function: time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605);        -- '01:00:05'

2. MySQL (date, days) conversion function: to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0 select to_days('2008-08-08'); -- 733,627

select from_days(0);           -- '0000-00-00'
select from_days(733627);      -- '2008-08-08'

3. MySQL Str to Date (string to date) function: str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y'); - the 2008-08-09 select str_to_date (' 08.09.2008 ', '% % % m. d. Y'); -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s'); - 08:09:30 select str_to_date (' 08.09.2008 08:09:30 ', '% % % % m. d. Y h: I: % % s'); -- 2008-08-09 08:09:30

As you can see, the str_to_date(str,format) conversion function can convert some messy strings to date format. In addition, it can also be converted to time. "format" can be found in the MySQL manual.

4. MySQL Date/Time to Str (date/time to string) function: date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008                             |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301                                     |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+ | time_format('22:23:01', '% s' % % h. i.) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 22.23.01 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

MySQL date and time conversion functions: date_format(date,format), time_format(time,format) can convert a date/time into a variety of string formats. It is a reversal of the str_to_date(str,format) function.

5. MySQL obtains the country time format function: get_format()

MySQL get_format() syntax:

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

Full examples of MySQL get_format() usage:

select get_format(date,'usa')          ;   -- '%m.%d.%Y'
select get_format(date,'jis')          ;   -- '%Y-%m-%d'
select get_format(date,'iso')          ;   -- '%Y-%m-%d'
select get_format(date,'eur')          ;   -- '%d.%m.%Y'
select get_format(date,'internal')     ;   -- '%Y%m%d'
select get_format(datetime,'usa')      ;   -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis')      ;   -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso')      ;   -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur')      ;   -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ;   -- '%Y%m%d%H%i%s'
select get_format(time,'usa')          ;   -- '%h:%i:%s %p'
select get_format(time,'jis')          ;   -- '%H:%i:%s'
select get_format(time,'iso')          ;   -- '%H:%i:%s'
select get_format(time,'eur')          ;   -- '%H.%i.%s'
select get_format(time,'internal')     ;   -- '%H%i%s'

The MySQL get_format() function is rarely used in practice.

6. MySQL codifies date and time functions: makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(31, 2001); -- '2001-01-31' select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

 

MySQL timezone conversion function convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

Time zone conversions can also be implemented by date_add, date_sub, timestampadd.

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00');      -- 2008-08-08 04:00:00

Latest comments