Online College Courses for Credit

+
1 Tutorials that teach Filter by Date
Take your pick:
Filter by Date

Filter by Date

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses the WHERE clause to filter data based on dates.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores filtering data based on dates and formatting date elements in three parts:

  1. Getting Started
  2. Date and Time
  3. Date Formatting

1. Getting Started

Working with dates in databases can be challenging as different databases will have different formats in which the dates are stored. In PostgreSQL, the date is formatted as yyyy-mm-dd. For example, for a September 15th, 2015 date, it would be formatted as 2015-09-15 in PostgreSQL. This is the same format that is used when we insert dates into a date column.

Note that we must quote the dates with single quotes like what we do with a string. For example, if we wanted to find all of the invoices that were submitted on January 1st, 2009, we would use the following query:

SELECT *
FROM invoice
WHERE invoice_date = '2009-01-01';

Notice that with the invoice_date in the invoice table, the data type is of type timestamp. We’ll get into that in the next part but be aware that this does exist.

File:11211-1160-1.png

This is useful to find one date, but you can use ranges for date parameters as well. For example, if we wanted to find all invoices prior to January 31st, 2009, we can use the < operator in the comparison instead of the = operator.

SELECT *
FROM invoice
WHERE invoice_date < '2009-01-31';

This will return the result set:


File:11212-1160-2.png

This method gives us more flexibility to control the date ranges.


2. Date and Time

There is also a datetime variable that stores both the date and time. The time is set as hh:mi:ss.mmm where mmm are in milliseconds.

There are some date-related functions that can be useful to use to get the current date and time. Note that these functions are based on the server date and time, so when you are running them, they may not be in your timezone. By default, on the PostgreSQL server that we use, the date and time are in GMT.

For example, you can use the following function in this query to get the current date and time:

SELECT now();

When run, you should see a result similar to the following:


File:11213-1160-3.png

Notice that the results come in the format yyyy-mm-dd and then T for time and hh:mi:ss.mmm and ending with a Z.

If you only wanted to get the date and not the time, you can do a conversion using:

SELECT now()::date;

This would return


File:11214-1160-4.png

We can also just get the time by using:

SELECT now()::time;

File:11215-1160-5.png


3. Date Formatting

We can change the format of the date using the TO_CHAR function. The TO_CHAR to convert the date takes in two parameters. The first parameter is the value that we want to format. The second is the template that defines the format. For example, if we wanted to output the current date in a mm/dd/yyyy format, we can do so by doing:

SELECT TO_CHAR(now()::date, 'mm/dd/yyyy');

File:11216-1160-6.png

There are many different template patterns for date formatting but some of the most common include:

  • hh – Hour of the day (01-12)
  • hh24 – Hour of the day (00-23)
  • mi – minute
  • ss – second
  • ms – millisecond
  • yyyy – year in 4 digits
  • yy – last 2 digits of the year
  • Month – the full month name with the capital first letter
  • month – the full month name in lowercase
  • Mon – the abbreviated month name
  • MM – month number (01-12)
  • Day – full capitalized day name
  • dd – day of the month
  • TZ – upper case time zone name

Considering what you see above, think about what this command would return and run it in PostgreSQL:

SELECT TO_CHAR(now(), 'Day, Month dd, yyyy hh24:mi ss tz');

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next try your own choices for which columns you want the query to provide.

summary
Working with date and time can be database-specific, but the core format is consistent between databases. There exist many different methods to extract specific date information from a timestamp data type.