By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

mysql date to timestamp

P: n/a
Ben
I would like to use php to query a database and retrieve a unix timestamp.

The problem is that mysql is storing the data in the date format and not
a timestamp.

I am sure that I can amend my query to format the date returned as a
timestamp without having to do the conversion in php.

Can someone tell me what to put in my db query?

Thanks

Ben.

Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ben wrote:

I would like to use php to query a database and retrieve a unix timestamp.

The problem is that mysql is storing the data in the date format and not
a timestamp.

I am sure that I can amend my query to format the date returned as a
timestamp without having to do the conversion in php.

Can someone tell me what to put in my db query?

Thanks

Ben.


I think this is what you're looking for (from mysql.com):
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01
00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date
argument, it returns the value of the argument as seconds since '1970-01-01
00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a
number in the format YYMMDD or YYYYMMDD in local time:
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the
internal timestamp value directly, with no implicit ``string-to-Unix-timestamp''
conversion. If you pass an out-of-range date to UNIX_TIMESTAMP() it returns 0,
but please note that only basic checking is performed (year 1970-2037, month
01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP() columns, you may
want to cast the result to signed integers. See section 6.3.5 Cast Functions.

Regards,
Shawn

--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com
Jul 17 '05 #2

P: n/a
In article <10****************@damia.uk.clara.net>, Ben wrote:
I would like to use php to query a database and retrieve a unix timestamp.

The problem is that mysql is storing the data in the date format and not
a timestamp.

I am sure that I can amend my query to format the date returned as a
timestamp without having to do the conversion in php.


What is the columntype for your date?

If you have TIMESTAMP, then you could do things as
SELECT FORMAT_DATE(date,'%d-%m-%Y %H:%i:%s') AS date

--
verum ipsum factum
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.