Formatting an SQL Timestamp with PHP

Formatting an SQL timestamp with PHP

The date function expects an UNIX timestamp as its second parameter -- which means you have to convert the date you get from the DB to an UNIX timestamp, which can be done using strtotime :

$db = '2010-02-20 13:14:09';
$timestamp = strtotime($db);
echo date("m-d-Y", $timestamp);

And you'll get :

02-20-2010

You were passing the '2010-02-20 13:14:09' string to the date function ; that string is not a valid UNIX Timestamp.

'12-31-69' is probably 1970-01-01, in your locale ; and 1970-01-01 is the Epoch -- the date that corresponds to the 0 UNIX Timestamp.

What is the simplest way to format a timestamp from SQL in PHP?

You could use MySQL to do this for you,

$result = mysql_query("SELECT DATE_FORMAT(my_timestamp, '%M %d, %Y) AS my_timestamp FROM some_table WHERE id=42", $DB_CONN);
$row = mysql_fetch_array($result);
$formatted_date = $row['my_timestamp'];
echo $formatted_date;

Or use PHP,

$result = mysql_query("SELECT my_timestamp FROM some_table WHERE id=42", $DB_CONN);
$row = mysql_fetch_array($result);
$formatted_date = strftime('%B %d, %y', $row['my_timestamp']);
echo $formatted_date;

How to convert MySQL TIMESTAMP to date time in PHP

To do this the OO (and most flexible) way use DateTime class and use the static createFromFormat method to instantiate a new DateTime object:

$new_datetime = DateTime::createFromFormat ( "Y-m-d H:i:s", $row["timestamp"] );

Now you can use the $new_datetime object to generate any string representation you'd like by calling the object's format method:

echo $new_datetime->format('d/m/y, H:i:s');

To boot, you since you've a DateTime object you can now also to any manner of transformation (like shifting timezones or adding days), comparison (greater or less than another DateTime), and various time calculations (how many days/months/etc... between this and another DateTime).

DateTime:http://php.net/manual/en/class.datetime.php
Learn it. Love it. Live it.

How can i take sql current timestamp and output as php date()?

Your problem is that you are using the wrong value in the second parameter. The date() function expects a UNIX-style timestamp as the second parameter, not a string representation of a date. Use the strtotime() function to correct this:

$somevar = date('Y-m-d H:i:s', strtotime($value['current_date']));

As someone else pointed out, however, why are you bothering to do this formatting? The format style you want is already in the database. It should be as easy as:

echo $value['current_date'];

PHP date() format when inserting into datetime in MySQL

The problem is that you're using 'M' and 'D', which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13

Try: date('Y-m-d H:i:s') which uses the numeric equivalents.

edit: switched G to H, though it may not have impact, you probably want to use 24-hour format with leading 0s.

How to format the date in PHP and SQL?

Try this:

$today = date("Y-m-d"); 
// 2011-09-24

Your string returns:

$today = date("F j, Y, g:i a");
// September 24, 2011, 6:39 am

---

Edit:

To save date and time you can use datetime or timestamp:

$today = time(); // timestamp

Change your database field to timestamp.

or

$today = date("Y-m-d H:i:s"); // datetime

Change your database field to datetime.

To select your data from database you can to this:

$result = mysql_query("SELECT date FROM table");
$row = mysql_fetch_array($result);

// field is timestamp
echo date("F j, Y, g:i a", $row['date']);
// or
// field is datetime
echo date("F j, Y, g:i a", strtotime($row['date']));

More informations here: http://at.php.net/manual/en/function.date.php

format mysql timestamp using php

try this.

date('F/j/Y',strtotime($result['postDate']));

as timestamp is required, not formatted date as second parameter.
or you can also try

SELECT UNIX_TIMESTAMP(postDate) as postDateInt from myTable

instead of SELECT postDate from myTable
and then have this in your code.

date('F/j/Y',$result['postDateInt']);

php:get only date from timestamp SQL

Go ahead and use PHP's DateTime class. I was reluctant to move away from functions to classes, but it's really not that hard. Just pass the date string into the class object and then use the format method to output the date string in the way you want.

http://php.net/manual/en/class.datetime.php

$dt = new DateTime('2012-07-24 11:52:01');
echo $dt->format('Y-m-d');

Change the format of a column to timestamp in MySQL

1. If your MySQL column is DATE type:

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

1. If your MySQL column is DATETIME type:

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

You haven't got to work strototime(), because it will not work with dash - separators, it will try to do a subtraction.

Update, the way your date is formatted you can't use strtotime(), use this code instead:

$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '$3-$2-$1 $4', $date); 
echo $date;

Output:

2009-07-02 00:07:00

---

Related Topics