Date issues

[mikawhat]

1. i've got the date stored in my db in this format: 2454007 . How can i convert it to dd/mm/yyyy?

2. i'd like to create a list of events. i've got all the events, old and upcoming, in a db and i'd like to display only the upcoming ones. e.g. SELECT * FROM events WHERE date....(after current date)... can anybody help me with the query?

[zabmilenko]

$time = time();
$how_many_seconds_ago = 3600 * 24 * 3;   // 3 days?

$query = "select * from db_table where date_field > ($time - $how_many_seconds_ago) order by date_field desc";

Then have some fun at: http://us2.php.net/manual/en/ref.datetime.php

[bogu]

1. What represents this format "2454007"?
2. If u create your field in table database as date type u can make your select like this:

   select * from db_table where date_field > CURDATE()

   U may find more about DATE & TIME FUNCTIONS here.

[mikawhat]

bogu wrote:

1. What represents this format "2454007"?

is the date from a script i downloaded and i'm trying to use it. However is not saved as a date but as an integer. How can i use it to perform the query?

[bogu]

But what is the format of that date I cant see the format ...

How do u know what date represents 2454007, format that number as dd/mm/yyyy ...

[NogDog]

If it's a UNIX timestamp, then it is 1970/01/29 09:40:07 GMT (as a result of the gmdate() function in PHP). If that doesn't sound like a valid date, then I have no idea without knowing the source of that integer and what it is supposed to represent.

[mikawhat]

2454007 i think it represents Sep 28th, 2006

[bogu]

mikawhat wrote:

2454007 i think it represents Sep 28th, 2006

And can u explain us how did u determine that?

[mikawhat]

look, is a script i bought which displays events. it has one of those small calendars where you select date. i selected a date and then i checked in the db and that's the number in the date column.

[bogu]

Until u understand how is the date format-ed u cant do anything ...

[mikawhat]

how? i can't check the code cause is encrypted. that's why i asked for help...

[NogDog]

And without being able to look at the source code or otherwise knowing what the relationship is between an arbitrary-seeming integer and the date it is supposed to represent, we are as in the dark as you.

[mikawhat]

OK. So what i should find out? How the date is stored as an integer instead of date? and why? what's the difference?

[NogDog]

The difference is that so far none of us knows what the number means. There is no obvious correllation (that I can see, anyway) between the integer value and the supposed date that it represents. Therefore we have no way of suggesting how to convert it to a human-readable format. If you can find out that it represents "x number of units since arbitrary time y", or that it is a key to another table/field in the database, or some other logic as to the source of that number; then I have no idea how to handle it.

[Installer]

It's a Julian date.

[Installer]

1:

$jd = 2454007;
echo date('d/m/Y', strtotime(jdtogregorian($jd)));

2:

$query = "SELECT * FROM events WHERE date > " . gregoriantojd(date('j'), date('n'), date('Y'));

[NogDog]

Installer wrote:

It's a Julian date.

Good catch! I knew they existed, but have never used them, so didn't recognize the format or even think of the possibility.

[Installer]

I'd been following the thread, and it didn't occur to me, either, until just then what it might be. Shows to go, eh? 🙂

[mikawhat]

excellent! good one Installer! I'll give it a try! Thank you all guys for your help 🙂