MySql and PHP timestamp with milliseconds

Select
UNIX_TIMESTAMP(DATE_FORMAT(d.`Entry_Date_and_Time`,'%Y-%m-%dT%H:%i:%s')) as TS




$formatted_TS = date("c",$TS);


$formatted_TS looks like 2009-07-13T08:30:44-05:00

That works great except that I can not figure out how to display milliseconds.

The original data is VARCHAR(17)  like 20090713083044416

I need to end up in a format like 2009-07-13T08:30:44.416-05:00
mcgilljdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ygouthamCommented:
there are simple mysql queries that can do the trick.

$testtime = "2010033419451456";  // March 23, 2010  19:45:14.56 is the time with milliseconds

$res = mysql_query("select date($testtime)");

$restime = mysql_query("select time($testtime)");

The above two queries would give you the date and time as you require.  Just use the two results and print out what you want.

Or one can always go the substring way and cut out the variables as per the length of the string and use it appropriately.

$year = substr($testtime,0,4);
$month = substr($testtime,4,2);

and so on and build the time to suit your needs
0
mcgilljdAuthor Commented:
How do I get the UTC offset?  I don't want to use a hardcoded offset that will change with daylight savings.
0
mcgilljdAuthor Commented:
I need to end up in a format like 2009-07-13T08:30:44.416-05:00
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

ygouthamCommented:
i do not see any UTC offset in the original string posted by you.  even the mysql query for date_format would not give you the offset.

probably you can look into the convert_tz() function to convert the time to some other standard time without a daylight saving and a second level converstion back for proper time with UTC offset.

0
ygouthamCommented:
what you can think of adding is a third variable after you get the date and time from the string.

select timediff(  utc_time(), time( now() )   );

the above query would give you the last part of the utc offset that you are asking for.  but daylight saving?? i dunno the answer for that
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcgilljdAuthor Commented:
$formatted_TS = date("c",$TS);

that php gives me the UTC offset.

You're right that I did not specifically say UTC offset, I just said I needed it to be formatted like 2009-07-13T08:30:44.416-05:00

I was hoping there was some straight forward way to include milliseconds.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.