dtmooir3395
2014-09-04 13:07
采纳率: 0%
浏览 85
已采纳

18位Julian Date / TimeStamp PHP转换?

I am currently working on a project for my work, it is using a lot of data from the mysql databases, alot of rows from the databases have a TimeStamp but they are 18 digits long.

I have a calculation that converts it into a friendly date in excel, DD-MM-YY HH:MM:SS.

The calculation is as follows (212262066420979000/86400000000) - 2415018.5 = value, and then format in excel into dd-mm-yy hh-mm-ss.

If I use php to do this calculation, I get the right value, but I can't set the format to look like a friendly date like you can in excel.

Is there any easy way of doing this in php?

Here is my code below

$sql = ("SELECT * FROM varmeas WHERE VARIABLE = 'WTSE';");

$result = mysqli_query($conn, $sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysqli_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

echo "<table border='1'>
<tr>
<th>ROUTING_POS</th>
<th>VARIABLE</th>
<th>VAR_MEASURE_1</th>
<th>DATETIME_STAMP</th>
<th>FRIENDLY_DATE</th>
</tr>";

while ($row = mysqli_fetch_assoc($result)) {

  echo "<tr>";
  echo "<td>" . $row['ROUTING_POS'] . "</td>";
  echo "<td>" . $row['VARIABLE'] . "</td>";
  echo "<td>" . $row['VAR_MEASURE_1'] . "</td>";
  echo "<td>" . $row['DATETIME_STAMP'] . "</td>";
  echo "</tr>";


}
echo "</table>";

?>

This would output a table like -

ROUTING_POS VARIABLE    VAR_MEASURE_1          DATETIME_STAMP   
FLIS        WTSE            22.20             212262066888394000    

图片转代码服务由CSDN问答提供 功能建议

我目前正在为我的工作项目工作,它正在使用来自mysql数据库的大量数据,很多 数据库中的行有一个TimeStamp,但它们长18位。

我有一个计算,它将它转换为excel中的友好日期,DD-MM-YY HH:MM:SS 。

计算如下(212262066420979000/86400000000) - 2415018.5 = value,然后在excel格式化为dd-mm-yy hh-mm-ss。 \ n

如果我使用php进行此计算,我会得到正确的值,但我无法将格式设置为像excel一样友好的日期。

有没有简单的方法在PHP中执行此操作?

以下是我的代码

  $ sql =(“SELECT * FROM varmeas  WHERE VARIABLE ='WTSE';“); 
 
 $ result = mysqli_query($ conn,$ sql); 
 
if(!$ result){
 echo”无法成功运行查询($ sql)  D B: ” 。  mysql_error(); 
退出; 
} 
 
if(mysqli_num_rows($ result)== 0){
 echo“未找到任何行,无需打印,因此退出”; 
退出; 
} \  n 
echo“&lt; table border ='1'&gt; 
&lt; tr&gt; 
&lt; th&gt; ROUTING_POS&lt; / th&gt; 
&lt; th&gt; VARIABLE&lt; / th&gt; 
&lt; th&gt; VAR_MEASURE_1&lt; / th&gt; \  n&lt; th&gt; DATETIME_STAMP&lt; / th&gt; 
&lt; th&gt; FRIENDLY_DATE&lt; / th&gt; 
&lt; / tr&gt;“; 
 
而($ row = mysqli_fetch_assoc($ result)){
 
 echo”&lt;  tr&gt;“; 
 echo”&lt; td&gt;“  。  $ row ['ROUTING_POS']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['VARIABLE']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['VAR_MEASURE_1']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['DATETIME_STAMP']。  “&lt; / td&gt;”; 
 echo“&lt; / tr&gt;”; 
 
 
} 
echo“&lt; / table&gt;”; 
 
?&gt; 
  <  / pre> 
 
 

这将输出一个表,如 -

  ROUTING_POS VARIABLE VAR_MEASURE_1 DATETIME_STAMP 
FLIS WTSE 22.20 212262066888394000 
    
 
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doucuoyan0426 2014-09-04 13:30
    已采纳
    (212262066420979000/86400000000) - 2415018.5 = value
    

    Gives a value of 41718.379872442, which is an MS Excel timestamp value for 20/03/2014 09:07:01

    To convert from an Excel timestamp to a Unix timestamp (based on the Windows 1900 calendar)

    For a quick and dirty conversion from an Excel timestamp to a unix timestamp that can be used with PHP date functions:

    $excelValue =  (212262066420979000/86400000000) - 2415018.5;
    $unixValue = ($excelValue - 25569) * 86400;
    

    Note that this will return a value in UST

    date_default_timezone_set('UTC');
    echo date('Y-m-d H:i:s', $unixValue);
    

    or

    $d = new DateTime('@' . (int)$unixValue, new DateTimeZone('UTC'));
    echo $d->format('Y-m-d H:i:s');
    

    EDIT

    To integrate it with your existing code is as simple as adding:

    $excelValue =  ($row['DATETIME_STAMP'] / 86400000000) - 2415018.5;
    $unixValue = ($excelValue - 25569) * 86400;
    $d = new DateTime('@' . (int)$unixValue, new DateTimeZone('UTC'));
    echo "<td>" . $d->format('Y-m-d H:i:s') . "</td>";
    

    immediately after

    echo "<td>" . $row['DATETIME_STAMP'] . "</td>";
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题