Formatting an SQL timestamp with PHP
Anyone know why? Edit: editedit: disregard that edit. the FTP addon for notepad++ timed out and unfortunately doesn’t display an error when it can’t synch.
If $r[‘timestamp’] is empty you’re going to get whatever time the system uses as the starting point for epoch time. (Which usually is 01-01-70 or 12-31-69)
6 Answers 6
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); 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.
Not sure why this was down voted — it is correct. You can use the strtotime php function or the UNIX_TIMESTAMP SQL method as suggested by Urda
@Simon : I was wondering, too, actually ;; I guessed I didn’t answer the question correctly, and was wondering what was the «correct way of understanding it» ^^ ;; Thanks for your note 🙂
you can use something like date(«G:i», $timestamp) — for more information see the manual php.net/manual/de/function.date.php
For starters, the php date() function is expecting seconds as the second variable. So that accounts for why your date is displaying wrong. Check this source on that issue.
Which then provides us the answer to the problem, to get PHP to format the date from a SQL timestamp correctly, we just change the query a tad.
SELECT author, UNIX_TIMESTAMP(`when`) Then use the PHP date function, with the variable that is storing the result of that above SQL query.
You could just use MySQL’s date_format() function instead:
SELECT date_format(timestampfield, '%m-%d-%Y') FROM table etc. This will save you having to round-trip your timestamp into unix time and then back into a normal date string in PHP. One datetime formatting call rather than two.
i think this will be useful to newble:
example basic subtraction 1 hour from date from MYSQL format:
$to='2013-25-10 22:56:00'; //curr time $timestamp = strtotime($to); //convert to Unix timestamp $timestamp = $timestamp-3600; //subtract 1 hour (3600 this is 1 hour in seconds) echo date("Y-m-d H:i:s",$timestamp); //show new date EDIT: After checking, it appears that MySQL returns a timestamp as a string to PHP, so this answer was bogus 🙂
Anyway, the reason you get a date in 1969 is probably that you’re converting a zero unix time from UTC to localtime. The unix time is the number of seconds since 1970. So a value of 0 means 1970. You probaby live in a timezone with a negative offset, like GMT-6, which ends up being 31-12-69.
ok, I was wrestling with this for a week (longer but i took a break from it).
I have two specific fields in tables
creationDate > timestamp > current_timestamp editDate > timestamp > current_timestamp
they were pulling out either dec 31 1969, or just nothing. annoying. very annoying
unix_timestamp(creationDate) AS creationDate unix_timestamp(editDate) AS editDate $timestamp = $result_ar['creationDate']; $creationDate = date("Y-M-d (g:i:s a)", $timestamp) echo($creationDate); $editstamp = $result_ar['editDate']; $editDate = date("Y-M-d (g:i:s a)", $editstamp) echo($editDate); this solved my problem for me returning
2010-Jun-28 (5:33:39 pm) 2010-Jun-28 (12:09:46 pm) I hope this helps someone out..
convert php date to mysql format
How do I convert this to MySql format 0000-00-00 for inclusion in db. Is it along the lines of: date(‘Y-m-d’ strtotime($date);. The reason I ask, is because I have tried variations of this and I cannot seem to get it to work. Either displays as 1970 or some other variation of that. Many thanks
9 Answers 9
$date = mysql_real_escape_string($_POST['intake_date']); 1. If your MySQL column is DATE type:
$date = date('Y-m-d', strtotime(str_replace('-', '/', $date))); 2. 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)/(\d)/(\d)\s(.*)#', '$3-$2-$1 $4', $date); echo $date; @bollo: Have you tried my code, yet? Give me a sample datetime to test, but I suspect it will work just fine.
it is still reversing the d & m. for example, using your code, after conversion, this is what appears in db: 2009-02-07 00:07:00 but it should be 2009-07-02 00:07:00. The response in firbug is showing the post as 02/07/2009 00:07:00. THANKS
@bollo: The way your date is formatted, it is not possible to use strtotime() . I updated my answer. Please check the code on the update part.
I am not up much on preg_replace. What exactly is code doing? also, how is my date formatted differently? I am using this format on other pages with no probs. As a test, i sent the response back through firebug json and this is the result: «date»: «2009-02-07 00:07:00», so it isn,t reaching the db correctly. Thanks
@bollo: The way you have your date right now, it is DD/MM/YYYY , it should be MM/DD/YYYY to work with strtotime() . The preg_replace() above will format the date to the output style I am showing above. Maybe you sent back the wrong variable, because I just double checked it, and it is responding with the way you want the date formatted.
This site has two pretty simple solutions — just check the code, I provided the descriptions in case you wanted them — saves you some clicks.
1.One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. The methods would be used as follows —
$mysqldate = date( 'Y-m-d H:i:s', $phpdate ); $phpdate = strtotime( $mysqldate ); 2.Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this —
$query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) WHERE. "; $query = "SELECT UNIX_TIMESTAMP(datetimefield) FROM table WHERE. "; Formatting date in PHP/MySQL query
How would I, using the DATE_FORMAT, get my fields start and end (both stored as DATETIME) to appear like this: 16/08/2013 00:00:00 etc? It is displaying the data properly, but I am not quite sure how to get DATE_FORMAT working.
What is the problem you are experiencing? Have you seen this reference? w3schools.com/sql/func_date_format.asp
2 Answers 2
Pass the proper format string to DATE_FORMAT in your query:
SELECT *, DATE_FORMAT(start, '%d/%m/%Y %H:%i:%s') AS the_date FROM mystation Then the formatted date will be available in a the_date column when you loop through your rows.
The following formatting string ,suggested by Marty McVry below, also works:
+1 for the reference to the MySQL documentation, and an example that returns a string in the specified format. But it would also be good to show an example of how to assign an alias to that column in the resultset. AS start_dmyt
Beside the solution bellow, that is right: WHEN working with data — in your case passing a date using a REST-API or something — you usually don’t want to apply formating. You should return the data AS RAW as possible, to allow the finally processing application to Format data as required.
use the common Format ‘2013-08-14 20:45:00 GMT+2’ (‘Year-Month-Day Hour:minute:second timezone’) so every Client is able to refactor the data retrieved in an appropriate way.
Data should be converted the Moment it is presented to the user — no second earlier.
(On the raw data Format of a date you can perform < , >, equals comparissions or sorting operations, without doing anything wrong.)
How can i convert a date in php to SQL format ? (and insert it)
I’m trying to insert a date , lets say CURDATE() to an sql DateTime field. My date is in the format of: 28/01/2008 When I try to insert it to the SQL, I get just zeroes. So how can I convert it properly?
5 Answers 5
$new_date = date('Y-m-d', strtotime($old_date)); Explanation
- strtotime() will try to parse a string ( $old_date in this case) and understand what date it is. It expects to be given a string containing an English date format or English textual datetime description. On success it will return a Unix timestamp (the number of seconds since January 1 1970). Now we have got a point in time out of that string.
- date() then will turn this previously obtained point in time to a format, described in the first parameter, in the example above it is the ‘Y-m-d’
- Y — A full numeric representation of a year, 4 digits
- m — Numeric representation of a month, with leading zeros
- d — Day of the month, 2 digits with leading zeros
- — — literally the minus symbol
Here’s a full list of characters you can use in the format parameter string
Look at my answer below, it’s because strtotime expects US date format when you’re using / as a separator, replacing it will indeed help. Refer to PHP Reference : date formats for more information.
I’m trying to insert a date , lets say CURDATE() to an sql DateTime field.
$timestamp = strtotime($old_date); $mydate = date('Y-m-d H:i:s', $timestamp); Since you’re using the European date notation (dd/mm/yyyy) the strtotime function will return 0 (which in turn will be converted to 1970-01-01 by date) if you don’t use — or . as separator.
So if you want to use strtotime, then you will have to change your date strings just a bit :
$olddate = '28/01/2008'; $newdate = strtotime(str_replace('/', '-', $olddate)); $newdate should now contain ‘2008-01-28’.
join('-',array_reverse(explode('/',$date))) to get the standard YYYY-MM-DD Mysql date format.
I’m trying to insert a date , lets say CURDATE() to an sql DateTime field.
Why don’t you use the MySQL function directly?
insert tbl (id, other, datecol) values(NULL, 'abcdef', CURDATE()); My date is in the format of: 28/01/2008
If it is not CURDATE() you want, but is a PHP variable in dd/mm/yyyy format instead, then see this MySQL man page for how to format the literals. Safe formats are YYYY-MM-DD and YYYYMMDD without time.
$date = '28/01/2008'; $query = "insert tbl (id, other, datecol) values(NULL, 'abcdef', '" . date('Ymd', strtotime($date)) . "');"; Note: yyyymmdd is also a valid format in SQL Server.