Page 1 of 1
Converting (mysql format) DAYS to timestamp..
Posted: Sat Aug 19, 2006 4:01 pm
by Jenk
A friend is trying to use Day number, ala MySQL does, and wants to convert format using date()
i.e. he has an integer which is the number of days since 1st Jan 1A.D. e.g. 732889 is Tuesday 1st August 2006.
however.. there is no table interaction and there is 000's of iterations, as such using mysql_query() to calculate it is a no-no at the moment.
Does anyone know of a function to convert this to unix timestamp?
Posted: Sat Aug 19, 2006 4:12 pm
by feyd
There's nothing built into PHP that could convert it. You'll have to either find an elaborate library or build it yourself I'm afraid.
Posted: Sat Aug 19, 2006 4:16 pm
by Jenk
dammit.
If only every year was 365days and every month 30days..
Posted: Sat Aug 19, 2006 4:22 pm
by feyd
If the data is coming from MySQL, alter the original selection. I'll assume that's not possible for whatever reason, so I would look into grabbing the source for MySQL and finding where the FROM_DAYS() function is written that way you can implement it in the same manor MySQL would handle it.
Posted: Sat Aug 19, 2006 4:25 pm
by Jenk
That's what we have been 'discussing' for the last 45mins, me trying to convince him to retrieve the data as a timestamp in the first place.
However he is adamant there is no interaction with the DB for it, which leaves me wondering where the number comes from..
Either way he has just succumbed and will be using unix timestamp and work in seconds instead.
Posted: Sat Aug 19, 2006 4:30 pm
by feyd
There is a possibility of using
strtotime() where you take the TO_DAYS() of the Epoch, diff the two then tell strtotime to give you the date from the epoch... ..that is if
strtotime() wants to play nice. You could pick a date further into the future (from the epoch that is.)
Code: Select all
mysql> select to_days(from_unixtime(0));
+---------------------------+
| to_days(from_unixtime(0)) |
+---------------------------+
| 719527 |
+---------------------------+
1 row in set (0.06 sec)
Posted: Sat Aug 19, 2006 6:19 pm
by Jenk
Thanks, I'm messing around with that now. However, when I run as you did above.. I get:
Code: Select all
mysql> select to_days(from_unixtime(0));
+---------------------------+
| to_days(from_unixtime(0)) |
+---------------------------+
| 719528 |
+---------------------------+
1 row in set (0.00 sec)
Posted: Sat Aug 19, 2006 6:38 pm
by feyd
You have to remember, my machine(s) are GMT-5

Posted: Sat Aug 19, 2006 6:43 pm
by Jenk
So according to US TZ Jesus was born 5 hours late? :p
That's supposed to be the number of days between 1st Jan 1AD, to 1st Jan 1970. I would of thought TZ is relevant here?
Posted: Sat Aug 19, 2006 6:49 pm
by volka
I am that friend
Posted: Sat Aug 19, 2006 7:21 pm
by Hat
Thank you all very much for all your help, comments and links.
I was just trying to get a "day number" expressed as an integer for any given date YYYY-mm-dd and to be able to switch between date and "day number" / "day number" and date at any time.. either using php or mysql and stumbled across the TO_DAYS and FROM_DAYS mysql - and thought this might give me what I need...
I think I have enough info now from your comments to be able to achieve what I need, so am going to chuck myself back into the various manuals and testing server for a bit
Much appreciated
Posted: Sat Aug 19, 2006 8:27 pm
by Jenk
^ being said friend
