[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

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
0
mcgilljd
Asked:
mcgilljd
  • 3
  • 3
1 Solution
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now