MySQL BigInt to Date

  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 480
  • Loc: India

Post 3+ Months Ago

Hi All,

I have a BigInt number eg: 1208319600000, i want to convert this number to date. As we convert the timestamp to date. the purpose for this is that i want to get the microsecond for the time also.

Can anyone help me to get this done.

Regards,
Amit
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Here's a preprocessor-independent way, since you didn't mention what else you're using.

Code: [ Select ]
SELECT CONCAT(FROM_UNIXTIME(LEFT(field, LENGTH(field) - 3)), '.', RIGHT(field, 3)) AS date_mili
  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 480
  • Loc: India

Post 3+ Months Ago

Thank you very much, i got what i wanted :). Thanks for your help.
  • dingusworrell
  • Born
  • Born
  • dingusworrell
  • Posts: 1

Post 3+ Months Ago

What is the syntax for MySQL 5? I have a column ctime as bigint in a system I inherited and am struggling to find the answer.

For example, if the data looks like this:
1227305503516


And I use this (the table is named "filesystem"):
select FROM_UNIXTIME( ctime, '%Y-%m-%d %H:%i:%s') from filesystem;

I get null results

Thanks
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

If you get null results, the first thing to check is that ctime isn't empty.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 61 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.