Mysql storage time, corresponding to Api and corresponding java properties

1. Mysql storage time type

Commonly used types of time/date storage:

  • DATE: Only used to store date values (year, month, day) in the format ‘YYYY-MM-DD’.
  • TIME: Only used to store time values (hours, minutes, seconds) in the format ‘HH:MM:SS’.
  • DATETIME: used to store date and time values at the same time, the format is ‘YYYY-MM-DD HH:MM:SS’.
  • TIMESTAMP: Used to store date and timestamp values, the format is the same as DATETIME, but it supports a wider time range.

Detailed introduction:

1.Date:

DATE is a data type of type Date. It takes a string representation of the format ‘YYYY-MM-DD’, where YYYY represents a four-digit year, MM represents a two-digit month, and DD represents a two-digit day. For example, ‘2021-08-16’ means August 16, 2021.

NOTE: When inserting dates, the correct format must be used. And the date must be valid. Otherwise, an error will be thrown or unexpected results will result.

Expiry date: ‘1000-01-01’ to ‘9999-12-31’

Extension:When querying date values, many built-in functions and operators can be used to perform various date operations. For example, you can use the YEAR(), MONTH(), and DAY() functions to get the year, month, and day information for a given date, respectively. You can also increase or decrease the number of days, weeks, months, etc. in a date using the DATE_ADD(), DATE_SUB() functions.

For example:

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  birth_date DATE
);

INSERT INTO my_table (id, name, birth_date) VALUES
  (1, 'Alice', '1990-07-01'),
  (2, 'Bob', '1985-12-15'),
  (3, 'Charlie', '1995-02-28');
select YEAR(birth_date) from my_table;

DATE_ADD() function explanation

The number of days, weeks, months, years, etc. in a given date value can be added using the DATE_ADD() function

Syntax: DATE_ADD(date, INTERVAL value unit)

date indicates the date value to be operated on; value indicates the value to be added or subtracted; unit indicates the time unit to be added or subtracted. The following time units are available

  • MICROSECOND: microseconds
  • SECOND: seconds
  • MINUTE: minutes
  • HOUR: hours
  • DAY: day
  • WEEK: week
  • MONTH: month
  • QUARTER: Quarter
  • YEAR: year

Example:

SELECT DATE_ADD('2018-01-01', INTERVAL 1 DAY);
-- Result: 2018-01-02

SELECT DATE_ADD('2021-08-16 10:00:00', INTERVAL 2 HOUR);
-- Result: 2021-08-16 12:00:00

SELECT DATE_ADD('1995-02-28', INTERVAL 1 MONTH);
-- Result: 1995-03-28

SELECT DATE_ADD('2022-01-01', INTERVAL 1 YEAR);
-- Result: 2023-01-01

SELECT DATE_ADD('2021-08-16', INTERVAL -1 DAY);
-- Result: 2021-08-15

Time formatting: DATE_FORMAT();

Note: “%Y-%m-%d” If big M, big D, it will display English

Small y will display the last two digits of the year

select DATE_FORMAT(birth_date,"%Y-%m-%d") from my_table;
select DATE_FORMAT(birth_date,"%m") from my_table;

2.TIME

TIME is a time data type used to store time values (hours, minutes, seconds).

It takes a string representation of the form ‘HH:MM:SS’, where HH is the two-digit hour, MM is the two-digit minute, and SS is the two-digit second. For example, ’23:59:59′ means 23:59:59

The valid time range for the TIME type is from ‘-838:59:59’ to ‘838:59:59’.

When querying temporal values, a number of built-in functions and operators are available to perform various temporal operations.

For example, you can use the HOUR(), MINUTE(), SECOND() functions to obtain the hour, minute, and second information for a given time, respectively. You can also use the TIME_FORMAT() function to format a time value into a specified string form.

TIME_FORMAT() use !

Syntax: TIME_FORMAT(time, format)

time indicates the time value to operate on; format indicates the string form to format the time value into.

  • %H: hour (00-23)
  • %h: hour (01-12)
  • %i: minutes (00-59)
  • %s: seconds (00-59)
  • %p: AM or PM
SELECT TIME_FORMAT('10:15:30', '%H:%i:%s');
-- Result: 10:15:30

SELECT TIME_FORMAT('22:45:15', '%h:%i:%s %p');
-- Result: 10:45:15 PM

Note: TIME_FORMAT(time,format), if the time parameter is not a time type (for example: an integer or a date), it will return a NULL value

3.DATETIME

The DATETIME type is a datetime data type used to store both date and time values.

It is represented as a string in the format ‘YYYY-MM-DD HH:MM:SS’, where YYYY is a four-digit year, MM is a two-digit month, DD is a two-digit day, HH Indicates hours, MM for minutes, and SS for seconds. For example, ‘2021-08-16 10:00:00’ means August 16, 2021 at 10 AM.

Time validity: 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59

You can use the YEAR(), MONTH(), DAY() functions to obtain the year, month, and day information of a given date; use the HOUR(), MINUTE(), and SECOND() functions to obtain the hour, minute, and second information for a given time .

You can also use the DATE_FORMAT() function to format a datetime value into a specified string form.

select DATE_FORMAT(created_at ,”%Y-%m-%d %H-%i-%s”) from my_table;

4.TIMESTAMP

TIMESTAMP is a datetime data type used to store date and time values. It is represented as a string in the format ‘YYYY-MM-DD HH:MM:SS’, where YYYY is a four-digit year, MM is a two-digit month, DD is a two-digit day, HH Indicates hours, MM for minutes, and SS for seconds.

Unlike the DATETIME type, the TIMESTAMP data type uses only 4 bytes for storage, thus taking up less disk space. In addition, the TIMESTAMP data type also supports the automatic update function, which can be automatically set to the current timestamp when a record is inserted or updated.

CREATE TABLE my_table2 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO my_table2 (id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

Valid period: ‘1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’

The TIMESTAMP data type uses a 32-bit integer to store a timestamp with a maximum value of 2^31-1 (that is, 2147483647), which is equivalent to 3:14:7 on January 19, 2038.

There are a number of built-in functions and operators that can be used to perform a variety of useful operations when querying and manipulating datetime values. For example, you can use the YEAR(), MONTH(), DAY() functions to obtain the year, month, and day information of a given date; use the HOUR(), MINUTE(), and SECOND() functions to obtain the hour, minute, and seconds information. You can also use the DATE_FORMAT() function to format a datetime value into a specified string form.

5. Other time types: (there are few actual development applications here)

  • YEAR: Used to store year information, the format is ‘YYYY’. The YEAR data type in MySQL stores 2 bytes and can represent years from 1901 to 2155.
  • INTERVAL: Used to store the amount of time between two dates or times, such as an hour or a month.
  • DAY, WEEK, MONTH, QUARTER, and YEAR: Used to perform arithmetic operations or comparisons on dates

Current date: select CURRENT_DATE() year month day

The function only returns a date value without time information

1. INTERVAL is DATE_ADD(date, INTERVAL value unit) in Example 1

2. Day SELECT DAY(‘2021-08-05’) —5

3. WEEK type

WEEK is a datetime data type used to store week number information. It takes an integer between 0 and 53 representing the week of the year.

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  week_of_birth WEEK
);

INSERT INTO my_table (id, name, week_of_birth) VALUES
  (1, 'Alice', 31),
  (2, 'Bob', 42),
  (3, 'Charlie', 22);

4. QUARTER

QUARTER is a datetime data type used to store quarterly information. It takes an integer between 1 and 4 representing the quarter of the year.

6. Corresponding Java attributes

In Java, different classes can be used to map datetime types in the database. Following are some common data types and their corresponding Java types:

  • DATE: java.sql.Date
  • TIME: java.sql.Time
  • DATETIME, TIMESTAMP: java.sql.Timestamp

in particular:

  • The DATE type is usually mapped to the java.sql.Date type in Java. java.sql.Date only contains year, month, day information, no time information. You can use the Date.valueOf(String) method to convert a string to a java.sql.Date type, or you can use the getDate() method to get a date value from a ResultSet.

  • The TIME type typically maps to the java.sql.Time type in Java. java.sql.Time only contains time information, no year, month, and day information. A string can be converted to a java.sql.Time type using the Time.valueOf(String) method, or a time value can be obtained from a ResultSet using the getTime() method.

  • The DATETIME and TIMESTAMP types are usually mapped to the java.sql.Timestamp type in Java. java.sql.Timestamp contains date and time information, accurate to the nanosecond level. A string can be converted to a java.sql.Timestamp type using the Timestamp.valueOf(String) method, or a datetime value can be obtained from a ResultSet using the getTimestamp() method.