Solved

php unix date format

Posted on 2013-01-21
17
516 Views
Last Modified: 2013-01-22
Can someone please tell me how to create a unix timestamp for the following date format (strtotime or other) whe it is entered into a database:

<?php
12/10/2013

?>

And then echo the date back onto a php page in the same format from the database:
<?php

?>
I know there are no database connection details but I just want the principle please
0
Comment
Question by:doctorbill
  • 9
  • 8
17 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38801073
Here's the background information you need to understand.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

To get a Unix Timestamp you can use time() if you want now, and you may be able to use strtotime() to convert a human-readable date to an integer timestamp.

For internal representations of dates you want to use the ISO-8601 format.  It removes the ambiguity that is expressed in 12/10/2013 by reformatting into YYYY-MM-DD.  Question: which date does that expression mean?

2013-12-10
2013-10-12

This will all be much easier for you when you start using the ISO-8601 format date/time values.  Then you can define your SQL table columns in the type of DATETIME.  When you want "pretty dates" for output you can use the combination of strtotime() and date() to reformat the dates as needed.

HTH, ~Ray

PS: Check this link: http://www.laprbass.com/RAY_strtotime.php?s=12%2F10%2F2013
PHP thinks that means Tuesday 10th of December 2013 12:00:00 AM
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801091
Here is the SSCCE:
<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('log_errors',     TRUE);
date_default_timezone_set('America/Chicago');


$str = '12/10/2013';
$tsp = strtotime($str);
$new = date('m/d/Y', $tsp);

// SHOW THE DATA VALUES
var_dump($str);
var_dump($tsp);
var_dump($new);

// SHOW THE RIGHT WAY TO STORE THE DATE/TIME IN THE DATABASE
$iso = date('c', $tsp);
var_dump($iso);

Open in new window

0
 

Author Comment

by:doctorbill
ID: 38801118
So:
This saves the date in the database:
<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('log_errors',     TRUE);
date_default_timezone_set('America/Chicago');


$str = '12/10/2013';
$tsp = strtotime($str);
$new = date('m/d/Y', $tsp);

How exactly will I use a php echo command to show it please?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801185
Please clarify this: And then echo the date back onto a php page in the same format from the database...

Does that mean you want to see the format that is stored in the data base?  Or the format of the original date string after a round trip to the data base?

Nothing shown here will save the date in the data base.  That would be done through a data base query that used INSERT or UPDATE.  The necessary first step is to get the format of the DATETIME information correct.  The article tells how to do that.

You might want to get this book if you're going to be doing PHP coding.  It is very readable and has great examples.
http://www.sitepoint.com/books/phpmysql5/
0
 

Author Comment

by:doctorbill
ID: 38801206
All I was after was the following:

<?php echo "what goes here" $databasestringdate ?>
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801268
I still am missing what you want to print out.

The data base should carry the date in ISO-8601 format.  You use a quoted string to INSERT or UPDATE a column of the DATETIME type.  Do you want to echo the ISO-8601 string?

The web page can format the date in any way that you want.  The formatting is done by the PHP date() function.  Do you want to echo the formatted date?

Lemme know, thanks.
0
 

Author Comment

by:doctorbill
ID: 38801310
yes please
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801342
Yes, please?  OK, but which one?  The data base ISO-8601 format?  Or the "pretty date" format of your choosing?  

Or do you want to see both formats?  If you want to see both, just install and run the script in the code snippet here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28002712.html#a38801091
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:doctorbill
ID: 38801370
both
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801435
OK, easy.  Check the comments in this (building on the earlier code snippet).  You can see it in action on my server here:
http://www.laprbass.com/RAY_temp_doctorbill.php

<?php // RAY_temp_doctorbill.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('log_errors',     TRUE);
echo '<pre>';

// THIS FUNCTION IS NECESSARY FOR USE OF date() and strtotime()
date_default_timezone_set('America/Chicago');

// HERE IS AN ORIGINAL DATA STRING SHOWING MONTH/DAY/YEAR
$str = '12/10/2013';

// THIS VARIABLE CONTAINS THE UNIX TIMESTAMP MADE FROM THE ORIGINAL DATA STRING
$tsp = strtotime($str);

// THIS SHOWS HOW TO CONVERT A UNIX TIMESTAMP INTO A "PRETTY DATE"
$new = date('m/d/Y', $tsp);

// SHOW THE DATA VALUES
echo PHP_EOL . 'ORIGINAL DATE STRING ';
var_dump($str);

echo PHP_EOL . 'UNIX TIMESTAMP FROM ORIGINAL DATE STRING ';
var_dump($tsp);

echo PHP_EOL . 'PRETTY DATE AFTER CONVERTING THE UNIX TIMESTAMP ';
var_dump($new);

// THIS FORMAT IS WHAT YOU WOULD USE TO STORE THE DATE/TIME IN THE DATABASE
$iso = date('c', $tsp);
echo PHP_EOL . 'ISO-8601 DATETIME VALUE FOR USE IN DB QUERIES ';
var_dump($iso);

// USE THIS TWO STEP PROCESS TO TURN THE ISO STRING SELECTED FROM THE DATA BASE INTO A "PRETTY DATE"
$tsp = strtotime($iso);
$new = date('m/d/Y', $tsp);

// YOU CAN ALSO USE THIS COMBINATION OF TWO FUNCTION CALLS
$new = date('m/d/Y', strtotime($iso));

// SHOW THE PRETTY DATE
echo PHP_EOL . 'PRETTY DATE AFTER CONVERTING THE ISO-8601 STRING ';
var_dump($new);

Open in new window

Best regards, ~Ray
0
 

Author Comment

by:doctorbill
ID: 38801443
I simply want to see a date format echoed from the saved string in the database into a php page
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801557
I hope we are almost there.  Please see line 39 of the most recent code snippet.

Here is the process:

Your script runs a SELECT query to acquire the row you want to examine.
Your script uses mysqli::fetch_assoc() or equivalent to bring the row back into the PHP variable scope.
Your script uses the instruction on line 39, substituting the name of the indexed position of the row in place of $iso.
Your script uses echo to visualize the $new variable.

Is this what you're looking for?  I'm a little concerned that we're still not connecting on the terminology.  For example, "date format" is a term of art in PHP.  It refers to the first argument to the date() function.  You can follow that link to the man page for date() to see what I'm talking about.
0
 

Author Comment

by:doctorbill
ID: 38801624
I have just uploaded a page called date_formats.php

I just want to be abel to enter a date value (example 21/01/2013) from the "dte1" text field into the database as a unix format
Then i just want to retreiev it  and display it as 21/01/2013

I just need a simple format

You can edit the page as appropriate
date-formats.php
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 38801988
Well, in the instant case, the date is unusable in the format of 21/01/2013.  The  rules for date conversion are shown in PHP.net.  Here is my test of that format :-/
http://www.laprbass.com/RAY_strtotime.php?s=21%2F01%2F2013

I'm sorry, but the Dreamweaver code you posted contains compound statements that make it impossible for me to understand how to get the client form input into the INSERT query.  I can answer questions and give examples, but when it comes to modifying code I'm at a bit of a loss.  I don't have your data base, so it would be impossible for me to test anything.

Here is the logical process you would want to integrate into your script.  You can experiment with it on my server.
http://laprbass.com/RAY_temp_doctorbill_3.php

<?php // RAY_temp_doctorbill_3.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');
echo "<pre>";

// IF THERE IS ANY CLIENT INPUT
$d = !empty($_GET['d']) ? $_GET['d'] : NULL;

// IF THE CLIENT PROVIDED A DATE
if ($d)
{
    // TRY TO CONVERT THE DATE TO A TIMESTAMP
    $t = strtotime($d);

    // IF THE DATE CONVERTED CORRECTLY
    if ($t)
    {
        // MAKE THE ISO-8601 STRING TO USE IN THE QUERY
        $i = date('c', $t);

        // SHOW THE INPUT AND ISO STRING
        echo PHP_EOL
        . 'CLIENT INPUT: '
        . "<b>$d</b>"
        ;
        echo PHP_EOL
        . 'ISO-8601 STRING TO BE USED IN THE QUERY: '
        . "<b>$i</b>"
        ;

        // AFTER THE QUERY, THE VALUE WILL BE RETURNED IN $i AND PROCESSED THIS WAY
        $n = date('m/d/Y', strtotime($i));
        echo PHP_EOL
        . 'FORMATTED IN MM/DD/YYYY: '
        . "<b>$n</b>"
        ;
    }

    // IF THE DATE FAILED TO CONVERT CORRECTLY
    else
    {
        echo PHP_EOL
        . 'UNUSABLE DATE STRING: '
        . $d
        ;
    }
}

// CREATE THE FORM FOR CLIENT INPUT USING HEREDOC NOTATION
$form = <<<FORM
<form>
Date: <input name="d" />
<input type="submit" value="Show the conversion" />
</form>
FORM;
echo $form;

Open in new window

And here is the script you posted earlier.  It looks to me like there would be something that you would need to do around line number 38-39, but I wouldn't know where to start without the risk of breaking other parts of the script.

<?php require_once('../../Connections/dev.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO date_formats (dte) VALUES (%s)",
                       GetSQLValueString($_POST['dte1'], "text"));

  mysql_select_db($database_dev, $dev);
  $Result1 = mysql_query($insertSQL, $dev) or die(mysql_error());
}

mysql_select_db($database_dev, $dev);
$query_dates_db = "SELECT * FROM date_formats";
$dates_db = mysql_query($query_dates_db, $dev) or die(mysql_error());
$row_dates_db = mysql_fetch_assoc($dates_db);
$totalRows_dates_db = mysql_num_rows($dates_db);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
  <label>Date1:
      <input type="text" name="dte1" id="dte1" />
  </label><br /><br />
  
  <label>Date2:
      <input type="text" name="dte2" id="dte2" />
  </label><br />
  
  
  <label>
      <input type="submit" name="submit" id="submit" value="Submit" />
  </label>
  <input type="hidden" name="MM_insert" value="form1" />
</form><br />
<p><?php echo $row_dates_db['dte']; ?></p>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($dates_db);
?>

Open in new window

If you read the code and comments I've posted here and you still are at a loss to know how to integrate this into the Dreamweaver scripts, then in respect of your time you might consider hiring a professional to help you get this working.  It won't take very long once a pro has access to your scripts on your server and that might get you a good result much faster than trying to do it all yourself, especially if you still do not have a good grasp on how PHP strtotime() and date() functions work.

That's the best I can do.  The question at this point is no longer about PHP Unix date format; that has been answered with tested and working code examples.  Now it's about how to modify Dreamweaver-generated PHP code. And for that you might want to post a new question in the Dreamweaver Zone.

Good luck with your project, ~Ray
0
 

Author Comment

by:doctorbill
ID: 38804991
Ray,
Can't thank you enough - extremely thorough as always

One final question:
I know I am a dodo for doing this  but I have entered a lot of records into my database with the following date format (day month and year):
21/08/2012
Is it possible to convert all these dates to the correct  number string using a sql query?
The reason I ask is that when I try to carry out searches in the database based on date range I get very strange results:
example - If I use a range 21/08/2012 to 21/09/2012 I get no records but if I reverse the range (21/09/2012 - 21/08/2012) I get results
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38805026
Yeah, that format is a problem because it's not a commonly understood date.  The month and day are ambiguous.

ASSUMING that the day is first and the month is second, you can follow this multi-step process...

Use ALTER TABLE and add a column of type DATETIME
Make a SELECT to get the existing date and the AUTO_INCREMENT key
Use the WHILE iterator to retrieve each row
With each row, use the following instructions to reformat the date (pidgin code)
$x = explode('/', $row['existing_date']);
$x = $x[2] . '-' . $x[1] . '-' . $x[0];
$x = date('c', strtotime($x));

Open in new window

UPDATE the current row to insert the new date string into the new column.

After that, use phpMyAdmin to look at the tables and see that the update went correctly.  When you're visually satisfied that the new columns contain useful data you can do one of the following:

1. Update your existing queries to use the new column name, or
2. Rename the old column to something like X_bad_date_column and rename the new column to the name you were already using for the old column.

Best of luck, ~Ray

Best of luck with it.
0
 

Author Closing Comment

by:doctorbill
ID: 38805251
Solved
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now