Hey all! Im having trouble parsing a Unix Timestamp. This is the scenario...

I have a script which inserts news posts. When mysql recieves this news it creates it an ID from auto increment and creates a Timestamp using the NOW() function. On my news.php page I have the following line which parses the Timestamp into the following format...

print date("d.m.y @ H:i:s", $row["date"]);

The problem is that the date() function isnt displaying the correct date, it displays:

19.01.38 @ 03:14:07 (as most recent post)

Help appeciated. Thanks,

    NOW() returns the time in this format: YYYYMMDDHHMMSS

    I believe UNIX_TIMESTAMP() will return a UNIX timestamp that you can use with the date() function.

    You can also use strtotime() to get a UNIX timestamp from the NOW() function.

      Hmm, when I edited the line to:

      print date("d.m.y @ H:i:s",   strtotime($row["date"]));

      I got a warning saying:

      "date(): Windows does not support dates prior to midnight (00:00:00), January 1, 1970"

      How can I make mysql use UNIX_TIMESTAMP instead of NOW as default? Thanks.

        get it with TIMESTAMP(NOW()). just checked the reference manual @ mysql.com, took me all of five minutes.

          Thanks. How do I apply "TIMESTAMP(NOW())"?

            just to clarify:

            "INSERT INTO table (date) VALUES ('TIMESTAMP(NOW())')"

            Is that the correct syntax?

              take out the quotes around the functions and it is 🙂

                I was kind of hoping mysql to handle the timestamp automatically, so I could omit the 'date' row when making INSERT queries. Is this possible in my position?

                PS: im using phpmyadmin.

                  I believe TIMESTAMP() still returns "2003-12-31 00:00:00". To get an actual UNIX timestamp, try UNIX_TIMESTAMP(NOW()).

                    what do you mean leave out the date line? the PHP function? when you SELECT you can use the DATE_FORMAT() function in mysql to get what you want. look it up on mysql.com

                      The field 'date' in my 'news' table is of type 'TIMESTAMP' so therefore I don't need to explicity send a timestamp to mysql because mysql handles this automatically when a new insert query is made. But when mysql uses TIMESTAMP it uses the NOW() function by default, how do i change this in phpmyadmin? Thanks.

                        take a look here. timestamps are updated automatically on insert, so you can just not specify that field and it will default to NOW(), then when you SELECT, you can use eitehr DATE_FORMAT or UNIX_TIMESTAMP and date() in PHP to get the format you want.

                        hth
                        moon

                          Thanks. I've got this working instead:

                          I altered the date field to type 'datetime' and im using php to parse the it with strtotime(). It works now thanks for all your help 🙂

                            Also, you can just change the date field to INT(10) then whenever you want to insert the time just use UNIX_TIMESTAMP() in the query, like so:

                            INSERT INTO database ( date , text )
                            VALUES (
                            UNIX_TIMESTAMP( ) , 'text'
                            );

                              Thanks python_q, im using your method, and it works a treat 🙂

                                always use mysql to format the date, always use datetime
                                str_to_time just wastes time.

                                INSERT INTO table (date) VALUES (NOW())
                                bugger timestamp let mysql do the work

                                $sql = mysql_query("SELECT Name, DATE_FORMAT(VisitTime, '%m.%d, %H:%i:%s') as vtime FROM STATS");
                                $query = mysql_fetch_array($sql);

                                other options
                                %Y: Long year (2002)
                                %M: Long month (August, September)
                                %m: Numeric month (08, 09)
                                %d: Day of the month (01)
                                %W: Long day of the week (Monday, Tuesday)
                                %a: Short day of the week (Mon, Tue)
                                %H: Hour (01, 02)
                                %i: Minute (01, 02)
                                %s: Second (01)

                                echo $query["vtime"];

                                there's my 2 cents worth

                                  Write a Reply...