PHP / MYSQL and Time

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
hmmm
Forum Newbie
Posts: 2
Joined: Tue Nov 23, 2010 3:05 pm

PHP / MYSQL and Time

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP / MYSQL and Time

Post 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.
Post Reply