Menu
I have a column in a MySQL database that contains a date as milliseconds (epoch). I want to build an SQL query that formats the date as something human readable (day, month, year, hours, minutes, seconds in any format and time zone). Is there an SQL (or MySQL-specific) function to do this?
953
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column.
You can use DATE() from MySQL to select records with a particular date. The syntax is as follows. SELECT *from yourTableName WHERE DATE(yourDateColumnName)='anyDate'; To understand the above syntax, let us first create a table.
MySQL DATE_FORMAT() Function The DATE_FORMAT() function formats a date as specified.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.
Try using the FROM_UNIXTIME function like this as given in the manual
SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19'
You could also use formatting like this
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), -> '%Y %D %M %h:%i:%s %x'); -> '2007 30th November 10:30:59 2007'
120
If you want to get the microsecond from a field, use %f,
select FROM_UNIXTIME(DATE_COLUMN/1000,'%Y-%M-%d %H:%i:%s %f') from TABLE_NAME; +-------------------------------------------------------+ | FROM_UNIXTIME(CREATETIME/1000,'%Y-%M-%d %H:%i:%s %f') | +-------------------------------------------------------+ | 2016-March-18 16:02:54 342000 | +-------------------------------------------------------+ Source : MYSQL DATE_FORMAT
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
