Asked  7 Months ago    Answers:  5   Viewed   48 times

I'm trying to display a datetime from my MySQL database as an iso 8601 formated string with PHP but it's coming out wrong.

17 Oct 2008 is coming out as: 1969-12-31T18:33:28-06:00 which is clearly not correct (the year should be 2008 not 1969)

This is the code I'm using:

<?= date("c", $post[3]) ?>

$post[3] is the datetime (CURRENT_TIMESTAMP) from my MySQL database.

Any ideas what's going wrong?

 Answers

96

The second argument of date is a UNIX timestamp, not a database timestamp string.

You need to convert your database timestamp with strtotime.

<?= date("c", strtotime($post[3])) ?>
Wednesday, March 31, 2021
 
superfell
answered 7 Months ago
21

Use SimpleDateFormat to format any Date object you want:

TimeZone tz = TimeZone.getTimeZone("UTC");
DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm'Z'"); // Quoted "Z" to indicate UTC, no timezone offset
df.setTimeZone(tz);
String nowAsISO = df.format(new Date());

Using a new Date() as shown above will format the current time.

Tuesday, June 1, 2021
 
anjan
answered 5 Months ago
70

Object Oriented

This is the recommended way.

$datetime = new DateTime('2010-12-30 23:21:46');

echo $datetime->format(DateTime::ATOM); // Updated ISO8601

Procedural

For older versions of PHP, or if you are more comfortable with procedural code.

echo date(DATE_ISO8601, strtotime('2010-12-30 23:21:46'));
Thursday, June 3, 2021
 
ALH
answered 5 Months ago
ALH
51

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '08/25/2012' is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):

  1. Configure Datepicker to provide dates in a supported format using an altField together with its altFormat option:

    <input type="hidden" id="actualDate" name="actualDate"/>
    
    $( "selector" ).datepicker({
        altField : "#actualDate"
        altFormat: "yyyy-mm-dd"
    });
    

    Or, if you're happy for users to see the date in YYYY-MM-DD format, simply set the dateFormat option instead:

    $( "selector" ).datepicker({
        dateFormat: "yyyy-mm-dd"
    });
    
  2. Use MySQL's STR_TO_DATE() function to convert the string:

    INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))
    
  3. Convert the string received from jQuery into something that PHP understands as a date, such as a DateTime object:

    $dt = DateTime::createFromFormat('m/d/Y', $_POST['date']);
    

    and then either:

    • obtain a suitable formatted string:

      $date = $dt->format('Y-m-d');
      
    • obtain the UNIX timestamp:

      $timestamp = $dt->getTimestamp();
      

      which is then passed directly to MySQL's FROM_UNIXTIME() function:

      INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))
      
  4. Manually manipulate the string into a valid literal:

    $parts = explode('/', $_POST['date']);
    $date  = "$parts[2]-$parts[0]-$parts[1]";
    

Warning

  1. Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables.

  2. Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions:

    This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

  3. You appear to be using either a DATETIME or TIMESTAMP column for holding a date value; I recommend you consider using MySQL's DATE type instead. As explained in The DATE, DATETIME, and TIMESTAMP Types:

    The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

    The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

    The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Friday, June 4, 2021
 
turik
answered 5 Months ago
46

I've tested this with JDK 1.8.0_131 for Mac OS X and JDK 1.8.0111 for Windows (both worked).

I've created a DateTimeFormatter with optional sections (delimited by []), to parse both cases (MM/dd/yyyy and yyyy-MM-dd'T'HH:mm:ss).

The same formatter worked for your case (LocalDate), but there are some considerations below.

// parse both formats (use optional section, delimited by [])
DateTimeFormatter parser = DateTimeFormatter.ofPattern("[MM/dd/yyyy][yyyy-MM-dd'T'HH:mm:ss]");

// parse MM/dd/yyyy
LocalDate d1 = LocalDate.parse("10/16/2016", parser);
// parse yyyy-MM-dd'T'HH:mm:ss
LocalDate d2 = LocalDate.parse("2016-10-16T10:20:30", parser);

// parser.format(d1) is the same as d1.format(parser)
System.out.println(parser.format(d1));
System.out.println(parser.format(d2));

The output is:

10/16/2016
10/16/2016


PS: this works only with LocalDate. If I try to format an object with time fields (like LocalDateTime), both formats are used:

System.out.println(parser.format(LocalDateTime.now()));

This prints:

06/18/20172017-06-18T07:40:55

Note that it formatted with both patterns. My guess is that the formatter checks if the object has the fields in each optional section. As the LocalDate has no time fields (hour/minute/second), the second pattern fails and it prints only the first one (MM/dd/yyyy). But the LocalDateTime has all the time fields, and both patterns are valid, so both are used to format.

My conclusion is: this isn't a general solution (like the Joda-Time's version), it's more like a "lucky" case where the patterns involved created the desired situation. But I wouldn't rely on that for all cases.

Anyway, if you are only using LocalDate, you can try to use this code. But if you're working with another types, then you'll probably have to use another formatter for the output, like this:

// parser/formatter for month/day/year
DateTimeFormatter mdy = DateTimeFormatter.ofPattern("MM/dd/yyyy");
// parser for both patterns
DateTimeFormatter parser = new DateTimeFormatterBuilder()
    // optional MM/dd/yyyy
    .appendOptional(mdy)
    // optional yyyy-MM-dd'T'HH:mm:ss (use built-in formatter)
    .appendOptional(DateTimeFormatter.ISO_LOCAL_DATE_TIME)
    // create formatter
    .toFormatter();

// parse MM/dd/yyyy
LocalDate d1 = LocalDate.parse("10/16/2016", parser);
// parse yyyy-MM-dd'T'HH:mm:ss
LocalDate d2 = LocalDate.parse("2016-10-16T10:20:30", parser);

// use mdy to format
System.out.println(mdy.format(d1));
System.out.println(mdy.format(d2));

// format object with time fields: using mdy formatter to avoid multiple pattern problem
System.out.println(mdy.format(LocalDateTime.now()));

The output is:

10/16/2016
10/16/2016
06/18/2017

Friday, July 2, 2021
 
Anax
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share