All Things Techie With Huge, Unstructured, Intuitive Leaps


MySQL is my database of choice, and its sql query language is part of the package.

In an SQL table, I have a series of transactions that are timestamped. The timestamp column is of type TIMESTAMP NOT NULL so for every transaction insert, the NOW() timestamp is inserted in the format yyyy-MM-dd HH:MM:SS. This is all fine and dandy except a timestamp is not really a date. However one can get a date with the following syntax:

SELECT DATE(timestamp_column) and one will get a date.

MySQL has a NOW() function, but I needed a TODAY() function to select all of the transactions occurring today from a timestamp column. It would work something like this:

SELECT * FROM transaction_table where transaction_timestamp = TODAY();

Of course transaction_timestamp is not a date so it would have to be re-worded to DATE(transaction_timestamp).

The way to get a TODAY() function is to use the following SQL statement:

SELECT * FROM transaction_table WHERE DATE(timestamp_column) = CURDATE();

CURDATE() is current date and it works like a charm.

No comments:

Post a Comment