Databases
StackOverflow2013
Postsdbo
POPHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa
    primarykey
    data
    text
    <p>im using php 5.4.6 and MySQL 5.5.29 and I get trouble by converting a UNIX TIMESTAMP to MYSQL DATETIME and vice versa. Mysql and php wun on the same local machine.</p> <p>I have a simple mysql table</p> <pre><code>CREATE TABLE Entry( id SERIAL PRIMARY KEY, created DATETIME NOT NULL); </code></pre> <p>To insert data I use php PDO and mysql NOW(). This works fine, the correct datetime is stored in the database.</p> <p>My plan is to work with unix timestamps on client side (php &amp; mysql on server side).</p> <p>So I would like to deliver unix timestamps to my client. Therefore I use MySql UNIX_TIMESTAMP() function to convert it directly in the query.</p> <p>So a sample query looks like this:</p> <pre><code>SELECT created, UNIX_TIMESTAMP(created) AS TS FROM Entry </code></pre> <p>The result: created = 2013-02-14 20:47:35 TS = 1360871255</p> <p>So now I want to do the other way, I pass the a UNIX Timestamp, and want to compare it with Entries in my Database. Unfortunetly Im not able to write a PHP script that works. I don't know why, but when I m passing the same timestamp (1360871255) to PHP I do not get 2013-02-14 20:47:35 with this method:</p> <pre><code>public static function toDateTime($unixTimestamp){ return date("Y-m-d H:m:s", $unixTimestamp); } </code></pre> <p>When I call toDateTime(1360871255) will return 2013-02-14 20:02:35 which is not the original DateTime.</p> <p>I know, I dont need to format 1360871255 to a Y-m-d H:m:s to use it in MYSQL, but 1360871255 seems not to be the time that I expected (and MYSQL UNIX_TIMESTAMP has returned).</p> <p>What I want to do is a simple query that shows me Entries that are older than a certain timestamp, something simple like this:</p> <pre><code>SELECT * FROM Entry WHERE created &lt; 1360871255 </code></pre> <p>but as I mentioned before, the query result is not the expected, because 1360871255 seems not to be the correct time.</p> <p>I do not specify any special timezone for the mysql connection in php.</p> <p>Any suggestions?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsockeqwe
    1. USsockeqwe
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload