Skip to main content
M
MartyMatthews
Participating Frequently
October 8, 2012
Answered

Convert MySQL timestamp field to a Unix timestamp

  • October 8, 2012
  • 2 replies
  • 2025 views

I have a process where I want to compare the current date with an expiration date that is so many months after a registration date. The registration date is stored in a MySQL timestamp format. I want to convert this to a Unix timestame so I can do the comparison with Time(). I have tried both the MySQL UNIX_TIMESTAMP and the PHP getTimestamp() functions and cannot get them to work. I have tried UNIX_TIMESTAMP both in  the original SELECT statement and as a stand alone one. I also get a divide-by-zero error with the date_default_timezone_set function. I have enclosed screenshots of the code and the result of the echo at the bottom. THANKS!

This topic has been closed for replies.
Correct answer David_Powers

You're making it far too complicated.

SELECT TO_DAYS(reg_date) - TO_DAYS(CURDATE()) AS daysleft

FROM ssregis

WHERE. . .

That will give you the number of days left until the registration expires. If it's less than 0, redirect to the renewal page.

By the way, you're laying yourself wide open to SQL injection attacks by failing to escape the input from the query string. At the very least, you should be doing this:

$entered_ss_id = mysql_real_escape_string($_GET['record_id']);

$entered_email = mysql_real_escape_string($_GET['email_address']);

However, since it looks as though you're hand-coding the script, you should be using MySQL Improved rather than PHP's deprecated MySQL functions. See http://docs.php.net/manual/en/mysqlinfo.api.choosing.php.

2 replies

David_Powers
David_PowersCorrect answer
Inspiring
October 8, 2012

You're making it far too complicated.

SELECT TO_DAYS(reg_date) - TO_DAYS(CURDATE()) AS daysleft

FROM ssregis

WHERE. . .

That will give you the number of days left until the registration expires. If it's less than 0, redirect to the renewal page.

By the way, you're laying yourself wide open to SQL injection attacks by failing to escape the input from the query string. At the very least, you should be doing this:

$entered_ss_id = mysql_real_escape_string($_GET['record_id']);

$entered_email = mysql_real_escape_string($_GET['email_address']);

However, since it looks as though you're hand-coding the script, you should be using MySQL Improved rather than PHP's deprecated MySQL functions. See http://docs.php.net/manual/en/mysqlinfo.api.choosing.php.

M
MartyMatthewsAuthor
Participating Frequently
October 8, 2012

THANKS a lot to both Bregent and David! I implemented both of  David's suggestions and they worked like a charm. I particularly appreciated the specific implementation into my code.

Can you tell me why

date_default_timezone_set(America/Vancouver);

gives me a divide by zero error?

Thanks again!

M
MartyMatthewsAuthor
Participating Frequently
October 9, 2012

MartyMatthews wrote:

Can you tell me why

date_default_timezone_set(America/Vancouver);

gives me a divide by zero error?

The timezone identifier must be a string:

date_default_timezone_set('America/Vancouver');


Thanks David! I shouldn't have had to ask!

B
bregent
Participating Frequently
October 8, 2012

I'm confused at what you are doing. Why not just use a MySQL date arithmatic function like DATE_ADD() to add the number of months to the reg date to arrive at the expiration date:

SELECT DATE_ADD (reg_date, INTERVAL ss_nomonths MONTH) as ExpireDate

or use a PHP date math function?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices