I am trying to write a query but I have run into a snag.<br /><br />I am trying to convert an UNIX timestamp to something MySQL would like. Everything works great if the date is after epoch, but on dates before epoch it doesn't work.<br /><br />For example, I have this query.<!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->SELECT FROM_UNIXTIME( -13391999 )<!--c2--></div><!--ec2-->This should convert to "1969-07-30 00:00:01" however it converts to <i>NULL</i>.<br /><br />How do I convert UNIX timestamps before epoch inside a query? <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/wallbash.gif" style="vertical-align:middle" emoid=":wallbash:" border="0" alt="wallbash.gif" /><!--content-->
From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).<br /><br />If your query is being constructed in PHP, you can use PHP to convert the timestamp, then place that result in your MySQL query:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$mydate = gmdate('Y-m-d H:i:s', -13391999);<!--c2--></div><!--ec2--><br />In the above example, $mydate will contain '1969-07-30 00:00:01'.<!--content-->
<!--quoteo(post=190931:date=Sep 29 2006, 04:14 PM:name=TCH-David)--><div class='quotetop'>QUOTE(TCH-David @ Sep 29 2006, 04:14 PM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190931"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).<br /><br />If your query is being constructed in PHP, you can PHP to convert the timestamp, then place that result in your MySQL query:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$mydate = gmdate('Y-m-d H:i:s', -13391999);<!--c2--></div><!--ec2--><br />In the above example, $mydate will contain '1969-07-30 00:00:01'.<!--QuoteEnd--></div><!--QuoteEEnd--><br />That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.<!--content-->
<!--quoteo(post=190933:date=Sep 29 2006, 04:08 PM:name=jhollin1138)--><div class='quotetop'>QUOTE(jhollin1138 @ Sep 29 2006, 04:08 PM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190933"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.<!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />According to <a href="http://archives.neohapsis.com/archives/mysql/2006-q1/1459.html" target="_blank">this</a> MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.<br /><br />Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?<!--content-->
<!--quoteo(post=190956:date=Sep 30 2006, 12:14 AM:name=telcor)--><div class='quotetop'>QUOTE(telcor @ Sep 30 2006, 12:14 AM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190956"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->According to <a href="http://archives.neohapsis.com/archives/mysql/2006-q1/1459.html" target="_blank">this</a> MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.<br /><br />Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?<!--QuoteEnd--></div><!--QuoteEEnd--><br />Actually I am migrating my existing phpBB forum to SMF. I had a birthday mod installed on my phpBB forum and wanted to bring this information over too. The phpBB birthday mod was storing data as the UNIX timestamp (well not directly) and SMF stores the data as a MySQL date stamp. I wanted to modify the existing conversion query for phpBB to SMF and discovered the problem with negative timestamps.<br /><br />I already wrote a PHP script that queries the phpBB database and updates the SMF database. I was just hoping to be able to modify the existing conversion script then to have to write a new one, which I did.<!--content-->
From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).<br /><br />If your query is being constructed in PHP, you can use PHP to convert the timestamp, then place that result in your MySQL query:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$mydate = gmdate('Y-m-d H:i:s', -13391999);<!--c2--></div><!--ec2--><br />In the above example, $mydate will contain '1969-07-30 00:00:01'.<!--content-->
<!--quoteo(post=190931:date=Sep 29 2006, 04:14 PM:name=TCH-David)--><div class='quotetop'>QUOTE(TCH-David @ Sep 29 2006, 04:14 PM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190931"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).<br /><br />If your query is being constructed in PHP, you can PHP to convert the timestamp, then place that result in your MySQL query:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$mydate = gmdate('Y-m-d H:i:s', -13391999);<!--c2--></div><!--ec2--><br />In the above example, $mydate will contain '1969-07-30 00:00:01'.<!--QuoteEnd--></div><!--QuoteEEnd--><br />That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.<!--content-->
<!--quoteo(post=190933:date=Sep 29 2006, 04:08 PM:name=jhollin1138)--><div class='quotetop'>QUOTE(jhollin1138 @ Sep 29 2006, 04:08 PM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190933"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.<!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />According to <a href="http://archives.neohapsis.com/archives/mysql/2006-q1/1459.html" target="_blank">this</a> MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.<br /><br />Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?<!--content-->
<!--quoteo(post=190956:date=Sep 30 2006, 12:14 AM:name=telcor)--><div class='quotetop'>QUOTE(telcor @ Sep 30 2006, 12:14 AM) <a href="http://www.totalchoicehosting.com/forums/index.php?act=findpost&pid=190956"><img src='http://www.totalchoicehosting.com/forums/style_images/1/post_snapback.gif' alt='*' border='0' /></a></div><div class='quotemain'><!--quotec-->According to <a href="http://archives.neohapsis.com/archives/mysql/2006-q1/1459.html" target="_blank">this</a> MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.<br /><br />Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?<!--QuoteEnd--></div><!--QuoteEEnd--><br />Actually I am migrating my existing phpBB forum to SMF. I had a birthday mod installed on my phpBB forum and wanted to bring this information over too. The phpBB birthday mod was storing data as the UNIX timestamp (well not directly) and SMF stores the data as a MySQL date stamp. I wanted to modify the existing conversion query for phpBB to SMF and discovered the problem with negative timestamps.<br /><br />I already wrote a PHP script that queries the phpBB database and updates the SMF database. I was just hoping to be able to modify the existing conversion script then to have to write a new one, which I did.<!--content-->