Page 1 of 1

Wierd Time Format : Convert

Posted: Mon Aug 29, 2005 1:30 am
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.

Posted: Mon Aug 29, 2005 6:17 am
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).

Posted: Mon Aug 29, 2005 6:46 am
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.

Posted: Mon Aug 29, 2005 5:23 pm
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?

Posted: Mon Aug 29, 2005 5:38 pm
by pickle
d11wtq wrote:one-way conversion to a julian date from a U.S. date/time format
You mean to Julian from "Gregorian"?

Posted: Mon Aug 29, 2005 6:49 pm
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?

Posted: Mon Aug 29, 2005 11:06 pm
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;
}

Posted: Tue Aug 30, 2005 10:03 am
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
)