SET @tz=@@session.time_zone ;
SET SESSION time_zone = '+0:00' ;
SELECT DATE_FORMAT(
FROM_UNIXTIME(1409304102.153) /*your epoch column here*/
,'%Y-%m-%d %a %H:%i:%s.%f GMT');
SET SESSION time_zone = @tz ;
>> 2014-08-29 Fri 09:21:42.153000 GMT
(or)
SELECT DATE_FORMAT(
FROM_UNIXTIME(1409304102.153) - INTERVAL (
TIMESTAMPDIFF(SECOND,UTC_TIMESTAMP(),NOW()) ) SECOND
,'%Y-%m-%d %a %H:%i:%s.%f GMT') ;
>> 2014-08-29 Fri 09:21:42.153000 GMT
-----Original Message-----
From: Philip Amadeo Saeli [mailto:psa...@zorodyne.com]
Sent: Monday, September 01, 2014 5:51 PM
To: Rajeev Prasad
Cc: MYSQL General List
Subject: Re: converting numeric to date-time?
* Rajeev Prasad <rp.ne...@yahoo.com> [2014-09-01 17:55]:
> I have a column in a table which is epoch time including milliseconds.
>
> e.g. = 1409304102153
>
>
> now i want to display all fields in the table but this field as: "2014-8-29
> Fri 09:21:42:xxxx GMT" (whatever comes in xxxx)
>
>
> and i am not finding anything on web about how to do that.
>
> can anyone help please.
>
> ty.
> Rajeev
I do not know how to do it directly in MySQL, but if you can dump the
table and post-process, this may be helpful on Linux: The date(1) cmd
can translate between formats, e.g. (taking the above value),
date -d "@1409304102.153" "+%Y-%m-%d %a %H:%M:%S.%N"
2014-08-29 Fri 04:21:42.153000000
--Phil
--
Philip Amadeo Saeli
openSUSE, CentOS, RHEL
psa...@zorodyne.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
