SQL current date — How to get the current date, time, month or year in postgreSQL?

Working with current dates and times in data science projects is quite common. In this episode of my SQL tutorial series I’ll show you the best functions that return the actual time and date — or part of them. I won’t just show you the SQL current date function, but many alternatives, so you can pick the one that fits your needs the best. Do you need the current:

• date,
• time,
• day,
• month,
• quarter,
• year,
• or even century?

This article will have you covered!

Note: This is going to be a hands-on tutorial, so I encourage you to do the coding part with me.

Note 2: If you are new here, start with these SQL articles first:

1. How to install Python, SQL, R and Bash (for non-devs)
2. SQL for Data Analysis – Tutorial for Beginners – ep1
3. How to install SQL Workbench for postgreSQL

Important! As I always say, the different SQL languages (mySQL, postgreSQL, MSSQL, etc.) are pretty similar to each other. That’s true — but the datetime functions are the exception in many cases. So it’s better if you know that this SQL tutorial is written for postgreSQL!

SQL current date

I’ll start with the simplest and most commonly used current date function.

Following from nomen est omen, the syntax is this:

SELECT current_date;

If you run it, you’ll see that it does what it promises:

It returns the current date. (Now you know when I wrote this article. ;-))

But there is more! (datetime, time, timestamp)

Of course, sometimes the date itself is not enough. Or it’s not exactly what you need. You might want to get the time — or the time and the date combined.

To get the current time in hours:minutes:seconds format (e.g. 15:43:01) in SQL, you’ll have to run this function:

SELECT current_time;

Hmm, you could have figured this out by yourself, too, right?

But let’s see what happens if you want to combine the two: when you need the current date and time in one cell. You’ll have many alternative solutions. I’ll show you 2 of these:

The first one is to combine SQL current_date and current_time:

SELECT current_date + current_time;

And if you are like me, you will hate to see ?column? in the place of the column name. So let’s use the AS keyword and name our column… I’ll go with current_datetime for the column name, but feel free to choose whatever you fancy:

And here’s an alternative (maybe even better) solution.

If you don’t want to bother with combining the two functions and renaming the column, you can go with the shorter built-in SQL function, which is:

SELECT current_timestamp;

The result will be the same.

Note: I prefer the SELECT current_timestamp; solution. But I showed you the SELECT current_date + current_time; variation as well for a reason. I think it’s important that you see that you can do different operations with times and dates in SQL. Anyway, I’ll write a whole article about it later.

Extracting date or time from an SQL timestamp

We have created a timestamp from current_date + current_time. Nice!

But it works the other way around, too. If you run the SQL current_timestamp function, you can “extract” the date and time figures from it.

Before that, here’s an SQL concept that you have to know of.

In SQL, there are a few different data types to work with date and time values. The data types that we have used so far in this article:

• time (e.g. 15:43:01)
• date (e.g. 2020-03-20)
• timestamp (e.g. 2020-03-20 15:43:01)

You always have to know exactly which one you’re using in your SQL tables and queries. You can’t mix them in a column, for instance… and more importantly, if you use them smartly, you can convert one into another, so it fits your needs better.

In data projects you’ll convert date/time data types quite often… And by far the most common “date-time-conversion” is to extract the date or the time values from a timestamp value.

Data type conversions in general can be done by using the double colon notation (::). It’s standard syntax in PostgreSQL.

In our specific example, it would look like this:

SELECT current_timestamp::time;

and

SELECT current_timestamp::date;

Put it together and rename the columns:

SELECT
current_timestamp::date AS my_date, 
current_timestamp::time AS my_time;

And you’ll get:

See? We extracted the current date and the current time separately from the current_timestamp SQL function…

Note 1: Do you have any alternative solution to get the results above?
Exactly — using this query: SELECT current_date AS my_date, current_time AS my_time; would result in the exact same output.

Note 2: most SQL date/time data types can be easily converted into other specific date/time data types. E.g. SELECT current_date::timestamp; works perfectly, and it returns something like: 2020-03-20 00:00:00. But be aware that there are a few exceptions — for instance, SELECT current_time::timestamp; won’t work and will return an error instead.

Extracting year, month, day, hour, minute or second values from an SQL timestamp (using the date_part function)

We can go even further!

In some data science projects, you’ll need only a specific part of your datetime value… Do you need the months only? The days? The seconds? Not a problem. Just use the date_part SQL function.

This is its logic:

SELECT date_part('[which_part_you_need]',
                 (SELECT current_timestamp));

It’s an SQL function within an SQL function. (At least, in our specific case — when we work with the current date/time.)

date_part takes two arguments:

• '[which_part_you_need]'
The [which_part_you_need] has to be replaced with the part of the date/time you need from your timestamp. (E.g. year, month, day, hour, etc.)
• (SELECT current_timestamp)
  And this function gets the current date and time… Important syntax requirement: this has to go between parentheses ()!

I’ll give you two examples to make this clear!

If you want to get the current year, just type this:

SELECT date_part('year', (SELECT current_timestamp));

And if you need the current minute value, run this line:

SELECT date_part('minute', (SELECT current_timestamp));

I won’t demo each and every different value here… But I encourage you to try it out for yourself.

Just run these SQL queries one by one to get the specific element of your current date/time:

• Current year: SELECT date_part('year', (SELECT current_timestamp));
• Current month: SELECT date_part('month', (SELECT current_timestamp));
• Current day: SELECT date_part('day', (SELECT current_timestamp));
• Current hour: SELECT date_part('hour', (SELECT current_timestamp));
• Current minute: SELECT date_part('minute', (SELECT current_timestamp));
• Current second: SELECT date_part('second', (SELECT current_timestamp));

date_part for every tiny detail

Well…
In fact, there are even more! Even though you don’t see them in your original timestamp value by default, you can extract any of these values from your current timestamp:

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

(Source: postgreSQL official documentation)

Just put any of the above keywords into the place of [which_part_you_need] in the query — and you’ll get it returned.

(Note: hey, if you read this article in 2999, you might very well need the millenium keyword!)

SQL current date — but in which time zone?

Here’s an extra comment for advanced players.

When I work on data science projects, I prefer to use a remote server and access it with an SQL manager tool (SQL Workbench or pgadmin4). I find this much better than writing SQL queries or commands directly to the command line. Most data scientists work the same way — and if you are a student in one of my courses (1, 2), you’ll work the same way, too.

If so, it’s better to be aware of this:

If you run SELECT current_timestamp; in the command line – so directly on your server -, you’ll get the current datetime in your server’s time zone. (Quite often, it’s GMT by default.)

But if you run SELECT current_timestamp; in your SQL manager tool (e.g. in SQL workbench), you’ll get the current datetime of your time zone (and not your server’s!)

While this looks like a minor thing, in specific cases, this can lead to confusion — or to some unexpected errors. (Believe me, I’ve seen many things! ;-))

Logical or not… this is how it works. There’s nothing really to do about it. But it’s better to be aware of it!

Conclusion

In this article we went through everything that you need to know about using SQL current date – and time – functions.

All together, these will cover everything you’ll need.

If they don’t, go ahead and check out the PostgreSQL documentation for more!

• If you want to learn more about how to become a data scientist, take my 50-minute video course: How to Become a Data Scientist. (It’s free!)
• Also check out my 6-week online course: The Junior Data Scientist’s First Month video course.

Cheers,
Tomi Mester

Cheers,
Tomi Mester