DBA tips & tricks: Querying for the current date!
A quick round-up of my favorite tips & tricks for database administration! If there is a difference in method /query structure between T-SQL, Postgres or MySQL I will make sure to point it out. The assumption is that the version of SQL is current to the date of this posting.
Querying for the current date!
There are a lot of scenarios you’ll want to get the server's current time. Say you want to throw some of the latest data you have into your ML model to train it, or you have to get a subset of your latest data to play around with! And yes, there's a case to simply “SELECT TOP([number]) * FROM[table] ORDER BY CreatedOn DESC” but we’re here to learn something so we continue.
There is a different method we need to familiarize ourselves with first for each procedural language:
T-SQL: GETDATE()
Postgres: CURRENT_DATE
MySQL: CURDATE()
Then we could use the method in a select statement to get the current server’s time, like so (a note on why simple selects never go out of fashion coming up soon!):
T-SQL: SELECT GETDATE()
With this foundational snippet we can now use it in a larger example. Let's say we want to find all of the records we created today using T-SQL. We might be tempted to query the following:
T-SQL: SELECT * FROM [TABLE] WHERE CAST(CreatedOn AS DATE) = GETDATE();
And nothing will come up (unless you have a very active database and the stars aligned⭐).
To understand why let’s remember that in different databases (version/years) the method might return different “stuff”. That “stuff” is an assumption, and since it *might* be what we think it is we *might* want to double-check ..by using the SELECT statement! 😃
If we do the SELECT statement in T-SQL then GETDATE() will give us “2021–02–17 9:39:10.660” while Postgres’ (pgSQL) CURRENT_DATE will give us “2021–02–17”. ..which is crazy, literally two different data types. One is a Date and the other is a Date/Time data type. If we simply assumed that it’s giving us the “Date” and nothing came up we might just think nothing of it and do a top(100) query instead.
These types of assumptions are the biggest mistakes programmers and DBA’s make. Knowing that these mistakes might happen will turn you from a good to a great programmer/DBA!
Also the correct answer to the query above would be:
T-SQL: SELECT * FROM Contact WHERE CAST(CreatedOn AS DATE) = CAST(GETDATE() AS DATE);
We’d just CAST both sides of the equation. Apples to apples! 🍏
One of my favourite websites for anything SQL is learnsql . They have a cookbook section that has easy-to-follow instructions on how to get the results you’re looking for in an aesthetically pleasing way (very on-trend).
