RE: converting numeric to date-time?
2014/09/04 08:40 -0700, Jan Steinman From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. MySQL s interpretation of timestamps is already such that not much such code is needed: see Overview of Date and Time Types. If this, 140930, really were September 30, 2014 it would be enough to write SELECT DATE(140930) This also works: SELECT CAST(140930210215 AS DATETIME) One does not need to write apart-tearing code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
From: Jan Steinman j...@ecoreality.org To: mysql@lists.mysql.com Sent: Thursday, 4 September 2014, 16:40 Subject: RE: converting numeric to date-time? From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153)/*your epoch column here*/ I don't think the OP has a Unix timestamp. Really? Looks like a unix epoch to me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
- Original Message - From: Jan Steinman j...@ecoreality.org Subject: RE: converting numeric to date-time? I don't think the OP has a Unix timestamp. OP explicitly says epoch including milliseconds - so it's going to be three digits too long :-) divide by 1000; split off decimal; from_unixtime(epoch) and add a millisecond display. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: converting numeric to date-time?
From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. Nobody talks more of free enterprise and competition and of the best man winning than the man who inherited his father's store or farm. -- C. Wright Mills Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: converting numeric to date-time?
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: GMT (whatever comes in ) 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.15300 --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
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: GMT (whatever comes in ) 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.15300 --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
