Page 1 of 1

PHP / MYSQL and Time

Posted: Tue Nov 23, 2010 3:16 pm
by hmmm
Hi,

I was wondering if someone could give me some advice on php /mysql with regards to time. Basically I want to enter the Unix Timestamp from the server into a field in my database. Should I use a php function to get the timestamp store it in a variable and then write that variable into an long integer column in a table? Also I will want to be using the timestamp when i extract data e.g. Only show records records whose "end date " timestamp is > than the existing timestamp. Is this possible am I going the right way about it? Also I presume I can do some arithmetic with timestamps? I.e add a weekd ? by adding (60X60 X24 x 7) seconds to the existing timestamp?

Also just checking, I can convert a unix timestamp into a date + time using a php function?

Am I approaching this in the right direction? I am new to working with time. XD, normally I would just use the SQL Now() function, but I don't think this is suitable, or are there more suitable sql methods?

Finally, I presume unix timestamps are based upon the servers time? if I set locale to UK, but run scripts of a USA server, will I have to adjust my timestamps to account for -5 hours? -1 X(60X60X5) ?

Thankyou for your help! :)

EDIT: if i use the date_default_timezone_set() function to change timezones, will i get the correct times / dates returned? e.g. if server is in usa, change the timezone set, to usa for calculations but output it with a different timezeone_set parameter? Will this work instead of manually offsetting?

Re: PHP / MYSQL and Time

Posted: Wed Nov 24, 2010 1:34 am
by requinix
- Unix timestamps can be used in many ways with PHP but they're less efficient in MySQL. DATETIMEs are very easy to deal with in MySQL but reformatting them is a hassle in MySQL and moreso in PHP.
- Start/end comparisons are just as easy with Unix timestamps as with DATETIMES. MySQL can add and subtract date intervals more easily with DATETIMEs; more easily with Unix timestamps in PHP.
- Do not do date arithmetic by yourself. You will get it wrong. Use MySQL (+/- INTERVAL x units) or PHP (strtotime) to do it.
- Yes, you can convert between the two in both MySQL (UNIX_TIMESTAMP() and FROM_UNIXTIME()) and PHP (strtotime() and date()).
- Yes, you're asking the right questions. Yes, NOW() is totally fine - if you go the DATETIME route.
- Unix timestamps have nothing to do with timezones. Check Wikipedia.
- date_default_timezone_get() will only affect the timestamp/date string conversions. It will not affect how strtotime() adds and subtracts times except for when the interval crosses a daylight savings boundary.