mysql timestamp conversion

MySQL is a relational database management system that is widely used to develop Web applications. One of the common questions is how to use MySQL timestamps to convert dates and times. MySQL's timestamp refers to the number of seconds from 00:00:00 on January 1, 1970 to the current time, and is usually used to record the time of event occurrence. In use, sometimes it is necessary to convert the timestamp into a specific date and time format. The following will explain in detail how to convert the timestamp.

In MySQL, the timestamp can be obtained through the UNIX_TIMESTAMP function, for example:

SELECT UNIX_TIMESTAMP()

The above command will display the current time Stamp value, similar to: 1570219906.

Unix timestamp is an integer representing the number of seconds that have elapsed from 00:00:00 on January 1, 1970 to the current date and time. Therefore, when dealing with date and time related operations, we need to convert the timestamp into a specific date and time format. The following are several common conversion methods:

1. Use FROM_UNIXTIME function for conversion

FROM_UNIXTIME function is a commonly used timestamp conversion function in MySQL. This function needs to pass in a timestamp value as a parameter and returns the date and time string corresponding to the timestamp. The following is an example:

SELECT FROM_UNIXTIME(1570219906)

Running the above command will return the result: '2019-10-04 07:45:06', which is the The standard time format corresponding to the timestamp.

2. Convert the timestamp to the DATE type

If we only need to obtain the date part of the information, we can convert the timestamp to the DATE type. The DATE type contains only date information, not time information. The following is an example:

SELECT DATE(FROM_UNIXTIME(1570219906))

This command will return the result: '2019-10-04', which is the timestamp. corresponding date.

3. Convert the timestamp to the TIME type

If we only need to obtain the time part of the information, we can convert the timestamp to the TIME type. The TIME type contains only time information, not date information. The following is an example:

SELECT TIME(FROM_UNIXTIME(1570219906))

This command will return the result: '07:45:06', which is the timestamp corresponding time.

4. Convert the timestamp to the DATETIME type

If you need to obtain date and time information at the same time, you can convert the timestamp to the DATETIME type, which contains date and time information. The following is an example:

SELECT DATE(FROM_UNIXTIME(1570219906)), TIME(FROM_UNIXTIME(1570219906))

This command will return the result: '2019-10- 04' and '07:45:06', which are the date and time corresponding to the timestamp.

It should be noted that the timestamp value from January 1, 1970 to the current time is a large integer. If you need to convert it into date and time format, you need to use some functions to process it. The functions listed above are commonly used conversion functions and can be selected and combined according to needs.

Summary

The use of timestamps to record the occurrence time of events is a widely used syntax in MySQL. During development, timestamps often need to be converted into specific date and time formats. In this article, we introduce several common conversion methods: conversion using FROM_UNIXTIME function, converting timestamp to DATE type, converting timestamp to TIME type, converting timestamp to DATETIME type. Note that the timestamp is an integer and needs to be processed for human reading. According to the actual needs of the project, you can choose the most suitable conversion method, extract useful date and time information, and achieve more convenient data processing effects.

The above is the detailed content of mysql timestamp conversion. For more information, please follow other related articles on the PHP Chinese website!