as3 - format a timestamp string from mysql

Question

I'm receiving a date field from a php file, and I need to format it. I get this string: "2009-11-12 17:58:13" I want to convert to "November 12, 2009 5:58:13 pm"

I tried to work with the Date class, but I get stuff like this: Type Coercion failed: cannot convert "2009-11-12 17:58:13" to Date.

Anyone know of any good utilities for doing this?

Answer 1

The built-in Date class has a parse() that should be able to parse that string into a real Date object. Then you could use the DateFormatter to turn it into a nicer string.

See also: Date parse() method reference

EDIT: Darn! I just noticed that parse() wants a format of Day Mon DD HH:MM:SS TZD YYYY

SECOND EDIT, assuming you can use mx.controls.DateFormatter:

import mx.formatters.DateFormatter;
var fmt:DateFormatter = new DateFormatter()
fmt.formatString = "MMMM D, YYYY L:NN:SS A"; 
var prettyDate:String = fmt.format("2009-11-12 17:58:13");

Answer 2

You can use the date() function to convert it on the PHP side. Check the documentation for the formatting rules you can use.

This might get you what you want:

date("F j, Y g:i:s a", $yourDateString)

Example: if you run:

echo date("F j, Y g:i:s a")

you get:

November 13, 2009 1:45:19 am

Answer 3

Okay, instead of changing it at the AS3 or PHP level, how about changing it at the source? If you have control of the SQL statement, you could use the MySQL date_format() function:

 select date_format(YOUR_DATE_COLUMN, "%M %d, %Y %l:%i:%s %p") from ...

Admittedly it's moving a responsibility of the display toolkit farther down to a different layer, and in doing so might make the SQL or PHP less reusable by other clients, but it also gets the job done. And in some cases the easy way is how it should be done.

btw, here's my test query and the output from it:

select date_format(
              cast("2009-11-12 17:58:13" as datetime), 
              "%M %d, %Y %l:%i:%s %p") 
from dual;

Output: November 12, 2009 5:58:13 PM

See also: MySQL date_format() function

Answer 4

If there is no straightforward solution, you can resort to regex:

private function formatDate(str:String):String
{

    var regex:RegExp = /(\d+)-(\d+)-(\d+)\s(\d+):(\d+):(\d+)/;
    var matches:Object = regex.exec(str);     
    var date:Date = new Date(matches[1], Number(matches[2]) - 1, matches[3], 
        matches[4], matches[5], matches[6]);
    var months:Array = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
        "Sep", "Oct", "Nov", "Dec"];
    var result:String = months[date.month] + " ";
    result += date.date + ", " + date.fullYear + " ";
    if(date.hours > 12)
     result += (date.hours - 12) + ":" + date.minutes + ":" + 
         date.seconds + " pm";
    else
     result += date.hours + ":" + date.minutes + ":" + date.seconds + " am";
    return result;
}

Explanation of the regex /(\d+)-(\d+)-(\d+)\s(\d+):(\d+):(\d+)/

// Forward slashes - / - at the beginning and end are delimiters

\d+ /* One or more number of digits. 
     * Ideally it should be \d{n} (\d{4} - 4 digits), 
     * but I'm assuming that the input string would always be 
     * valid and properly formatted
     */

- and : //A literal hyphen/colon 

\s   //A whitespace (use \s+ to skip extra whitespaces)

()  /* Parenthetic groups used to capture a string for using it later
     * The return value of regex.exec is an array whose first element is 
     * the complete matched string and subsequent elements are contents
     * of the parenthetic groups in the order they appear in the regex
     */

Answer 5

yeah you can format date in the query using the date_format mysql function. This website will help you format the date http://www.mysqlformatdate.com