Comparing timestamp in MySQL

We often use timestamps in databases. In fact people often use it to make session keys. Comparing timestamp is also often required when detecting any change in database. You can read rest of the post on my personal website, here is the link.

My personal website is up and all new blog posts will only be available on https://neerajkhandelwal.com.

That’s too much often. 😛

Required: MySQL

Using:

Table defination

table1(
id (int)
jointime(timestamp))

SQL query

SELECT id FROM table1 WHERE jointime > '2011-10-08 00:00:00';

Explantion:

  1. This query selects id from table where jointime is greater than 2011-10-08.

Note:

  • Timestamp is a standard data type in MySQL and it should have both date and time.
  • For current time use NOW()
  • If you are using only date then use conversion from date to timestamp using TIMESTAMP()
  • With PHP
    • First get the time if current time use time(), it will give time in Unix Epoch.
    • Convert above time to timestamp using date() and then use in query.
    • If using some other time use mktime().
  • Remember the quotes they are important.

Also see MySQL timestamp datatype and PHP date time functions, also other functions.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s