Wierd Time Format : Convert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Wierd Time Format : Convert

Post by facets »

Hi all,

I've come across some wierd dates i'm trying to convert to readable text.
In the database they are displayed as "38128.2083333333"

When running the Crystal report over them they use the following function
datetime({stdrptlist.schstartrun})

Can anyone help me with the conversion? Is it the Gregorian calender?

Cheers, Will.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

It looks like it may be a Julian date (but I might be way off). i can soon check since a recent project required work with julian dates (and the conversion).
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

This is the julian date today... (as of 5 mins ago)

2453612.02917

Bear in mind that Julian dates go back to before 4000 B.C. (number of days since then). Are you working on something historical? You're date is a while before mine. It gets very tricky if so since you can't use the unix time functions in PHP. I have some custom written stuff but it's a one-way conversion to a julian date from a U.S. date/time format. I'm willing to have a stab at reversing it though.

Googling may help too.

Things you will need to know (or should set to zero if unsure) are, The UTC offset, whether daylight savings time applies. That's only if you want to be right down the very last hour however.
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

Here's the spanner in the works.
My date was from yesterday also, well or today (Recent anyway).

The program that's writing the dates is VB and is going into a MySQL DB. I don't have access to the VB code either.

After some further investigation the date should look more like 38601.4397 (5 then 4 digits) rather than what I posted earlier.

Does that help at all?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

d11wtq wrote:one-way conversion to a julian date from a U.S. date/time format
You mean to Julian from "Gregorian"?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

I can now convert this date in excel! (Sorry to mention that word here!)
Format cell / Custom Date dd/mm/yyyy h:mm AM/PM

But I still have no idea on how to convert that in PHP.
Can anyone shed any light?
User avatar
sweatje
Forum Contributor
Posts: 277
Joined: Wed Jun 29, 2005 10:04 pm
Location: Iowa, USA

Post by sweatje »

I use the following function to convert a unix time stamp to an excel date. You should be able to reverse the math to get a unix time stamp from your excel date.

Code: Select all

function to_excel_date($date_string) {
	$dt = strtotime($date_string);
	return (gmmktime(0,0,0,date('m',$dt), date('d',$dt), date('Y', $dt)) +86400*25569)/86400;
}
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

It sounds like you're using Windows, so you can use the built-in calendaring functions to convert.
PHP Manual wrote: cal_from_jd

(PHP 4 >= 4.1.0, PHP 5)
cal_from_jd -- Converts from Julian Day Count to a supported calendar
Description
array cal_from_jd ( int jd, int calendar )

cal_from_jd() converts the Julian day given in jd into a date of the specified calendar. Supported calendar values are CAL_GREGORIAN, CAL_JULIAN, CAL_JEWISH and CAL_FRENCH.

Example 1. cal_from_jd() example
<?php
$today = unixtojd(mktime(0, 0, 0, 8, 16, 2003));
print_r(cal_from_jd($today, CAL_GREGORIAN));
?>

The above example will output:

Array
(
[date] => 8/16/2003
[month] => 8
[day] => 16
[year] => 2003
[dow] => 6
[abbrevdayname] => Sat
[dayname] => Saturday
[abbrevmonth] => Aug
[monthname] => August
)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply