Dates in PHP & MySQL

It took me a while to get my head around usign dates in PHP and MySQL but it’s actually quite simple. I’m not sure what order to talk about this in so I’ll just go ahead and ramble.

The MySQL NOW() function can be used in an SQL string to insert the current date and time into a field. Like this:

INSERT INTO dates (the_date) VALUES (NOW())

Pretty straight forward but very useful. Of course you can also set the “on update CURRENT_TIMESTAMP” option in MySQL so that any time the record is altered the timestamp will be updated with the current time. This is useful for “last modified” attributes on things like blog posts.

Timestamps are the number of seconds since a given time. Unix timestamps are the number of seconds since 01-01-1970 00:00:00. They’re good for computers but basically unreadable by humans.

Some of PHPs date functions take a timestamp as an argument so while they’re a little confusing and you’re bound to need them at some point.

Personally I think a nicely formatted string like “20091001124212” could make life a whole lot easier but there’s reasons why it doesn’t work like that, so just get used to timestamps!

PHP Manual PageW3Schools Page

An easy way to make a timestamp is the mktime (aka MaKe TIMEstamp) function in PHP. The basic syntax is as follows:

mktime(hour, min, sec, month, day, year[, is_dst])

It’s pretty straight forward, just stick the appropriate numbers in one end and a timestamp falls out the other. The is_dst means “is daylight saving time” which should be used if your PHP version is less than 5.

You can do date addition/subtraction with this function, too. For example:
mktime(0, 0, 0, date("m"), date("d")+100, date("y"))
would give you a timestamp for 100 days in the future.

Mktime() will spazz out if you give it values with leading zeros. For example the year 09 would be interpreted as 0. The solution? Make sure you don’t use leading zeros. For the year value, try to use four digit numbers to avoid ambiguity.

PHP Manual PageW3Schools Page
The date() function formats a date/time in the format of your choice. It takes one or two arguments: date($format_string[, $timestamp]). If $timestamp is not given the current time is used. The format string is made up of special characters, each of which denotes a way of displaying the hour or minute or month etc. For example ‘d/m/y’ would give you (at the time of writing) 01/10/09. A complete list of available special characters follows:

  • d – Day of month (01 – 31)
  • D – Day as text (three letters)
  • j – Day of month, no leading zeros (1 to 31)
  • l (lowercase ‘L’) – Day, full text
  • N – The ISO-8601 numeric representation of a day (1 for Monday through 7 for Sunday)
  • S – The English ordinal suffix for the day of the month (2 characters st, nd, rd or th. Works well with j)
  • w – A numeric representation of the day (0 for Sunday through 6 for Saturday)
  • z – The day of the year (from 0 through 365)
  • W – The ISO-8601 week number of year (weeks starting on Monday)
  • F – A full textual representation of a month (January – December)
  • m – month number (01 – 12)
  • M – A short textual representation of a month (jan – dec)
  • n – Month number no leading zeros (1 to 12)
  • t – The number of days in month
  • L – Leap year (1 if leap year, 0 otherwise)
  • o – The ISO-8601 year number
  • Y – Year, four digits (2009)
  • y – A two digit representation of a year (09)
  • a – Lowercase am or pm
  • A – Uppercase AM or PM
  • B – Swatch Internet time (000 to 999)
  • g – 12-hour hour (1 to 12)
  • G – 24-hour hour (0 to 23)
  • h – 12-hour hour (01 to 12)
  • H – 24-hour hour (00 to 23)
  • i – Minutes, leading zeros (00 to 59)
  • s – Seconds, leading zeros (00 to 59)
  • e – The timezone identifier (Examples: UTC, Atlantic/Azores)
  • I (capital i) – Daylights savings time (1 if Daylight Savings Time, 0 otherwise)
  • O – Difference to Greenwich time (GMT) in hours (Example: +0100)
  • T – Timezone setting of the PHP machine (Examples: EST, MDT)
  • Z – Timezone offset in seconds. The offset west of UTC is negative, and the offset east of UTC is positive (-43200 to 43200)
  • c – The ISO-8601 date (e.g. 2004-02-12T15:19:21+00:00)
  • r – The RFC 2822 formatted date (e.g. Thu, 21 Dec 2000 16:01:07 +0200)
  • U – The seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

This PHP function takes a string written in normal English and attempts to convert it into a timestamp. For example:

strtotime('next Thursday');
strtotime('+7 hours');
strtotime('+1 week');

On failure function returns false on PHP5 and -1 on earlier versions.

For a full list of PHP date/time functions check out the w3schools page.


About Mr Chimp

I make music, draw pictures, browse the internet, programme, and make sweet, sweet cups of tea until the early hours.
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s