Convert unix timestamp in seconds to readable date in MySQL / MariaDB
ID: 368
Category: MariaDB Database
Added: 12th of January 2024
Views: 1,681
The following table has a list of users with the following fields:
user_id
registration_date
name
country
To convert the field registration_date in seconds to readable date in MySQL / MariaDB enter the following query
select user_id, FROM_UNIXTIME(registration_date, '%d/%m/%Y %h:%i:%s') as formatted_date, name, country from tbl_users_example ORDER BY registration_date ASC;
The operators to format the date
%d = Date
%m = Month
%Y = Year
%h = Hours
%i = Minutes
%s = Seconds
The date was set in UK Format. For US date format you would enter the following
select user_id, FROM_UNIXTIME(registration_date, '%m/%d/%Y %h:%i:%s') as formatted_date, name, country from tbl_users_example ORDER BY registration_date ASC;
