How to Convert Timestamp to Date and Time format in MySql. As Timestamp is not a human readable format, Many times we need to convert MySql timestamp to date format.
this tutorial, I am going to discuss how to convert a timestamp to human readable date and time format in MySql.
How to Convert TimeStamp to Date and Time Format in MySql
We can easily convert MySql Timestamp to Date and Time format using an inbuilt functions in MySql.
MySql provides FROM_UNIXTIME() method, using this method you can easily convert TimeStamp to Date and Time format.
Syntax:
|
1 |
FROM_ UNIXTIME (unix_timestamp, [format ]) |
It takes a timestamp as an argument and output a value in ‘YYYY-MM-DD HH:MM:SS‘ format. You can also convert the timestamp into other formats using modifiers.
Convert Timestamp to Date and Time Format in Mysql
|
1 2 3 4 5 |
// Print current date and time SELECT from_unixtime(unix_timestamp()) /* Output */ 2020-12-26 12:17:42 |
|
1 2 3 |
//Convert input timestamp into date and time. select from_unixtime(timestamp) from tablename; |
1. How to extract date from timestamp in MySQL
We have learned how to convert a timestamp into date and time format. Suppose, we have to only extract date part as we don’t want to print time.
To extract and print a date, we can write a query like this DATE( FROM_UNIXTIME( timestamp ) ).
How to install MySql workbench
First, FROM_UNIXTIME convert a timestamp to date and time format and then we are using DATE function to extract a date.
|
1 2 3 4 5 6 7 8 9 |
/* Let's extract and print a date */ mysql> SELECT DATE( FROM_UNIXTIME( unix_timestamp() ) ) as date; +------------+ | date | +------------+ | 2020-06-26 | +------------+ 1 row in set (0.00 sec) |
|
1 2 |
/* Similarly you use a column name */ select DATE( FROM_UNIXTIME( columnname ) ) from tablename; |
2. How to extract time from a timestamp in MySQL
To print only time, we can use TIME(FROM_UNIXTIME(timestamp)) .
|
1 2 3 4 5 6 7 8 9 |
// Prints only time mysql> SELECT TIME( FROM_UNIXTIME( unix_timestamp() ) ) as time; +----------+ | time | +----------+ | 16:46:02 | +----------+ 1 row in set (0.00 sec) |
Time() method prints the time in HH:MM:SS format. You can further extract and print hour. To do this MySql provides HOUR() method. It takes time as an argument and print hour.
|
1 2 3 4 5 6 7 |
mysql> SELECT HOUR( FROM_UNIXTIME( unix_timestamp() ) ) as hour; +------+ | hour | +------+ | 16 | +------+ 1 row in set (0.00 sec) |
3. DATE_FORMAT() function in MySQL to convert MySql timestamp to date and time
It formats the date and time value as per format string you specify.
|
1 2 3 |
Syntax: DATE_FORMAT(date,format) |
Print only time
|
1 |
SELECT DATE_FORMAT( FROM_UNIXTIME( unix_timestamp() ),'%H:%i:%s' ) |
Similarly, you can print the date and time in any format. Here is a list of format specifier use by DATE_FORMAT()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
%a for weekday name (Sun..Sat) %b month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal “%” character %x x, for any “x” not listed above |
