Working with dates
Question
How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.
Expected Results
| memid | surname | firstname | joindate |
|---|---|---|---|
| 24 | Sarwin | Ramnaresh | Sat Sep 01 2012 08:44:42 GMT+0000 (Coordinated Universal Time) |
| 26 | Jones | Douglas | Sun Sep 02 2012 18:43:05 GMT+0000 (Coordinated Universal Time) |
| 27 | Rumney | Henrietta | Wed Sep 05 2012 08:42:35 GMT+0000 (Coordinated Universal Time) |
| 28 | Farrell | David | Sat Sep 15 2012 08:22:05 GMT+0000 (Coordinated Universal Time) |
| 29 | Worthington-Smyth | Henry | Mon Sep 17 2012 12:27:15 GMT+0000 (Coordinated Universal Time) |
| 30 | Purview | Millicent | Tue Sep 18 2012 19:04:01 GMT+0000 (Coordinated Universal Time) |
| 33 | Tupperware | Hyacinth | Tue Sep 18 2012 19:32:05 GMT+0000 (Coordinated Universal Time) |
| 35 | Hunt | John | Wed Sep 19 2012 11:32:45 GMT+0000 (Coordinated Universal Time) |
| 36 | Crumpet | Erica | Sat Sep 22 2012 08:36:38 GMT+0000 (Coordinated Universal Time) |
| 37 | Smith | Darren | Wed Sep 26 2012 18:08:45 GMT+0000 (Coordinated Universal Time) |
Your Answer
Your Results
Loading database...
Answers and Discussion
select memid, surname, firstname, joindate
from cd.members
where joindate >= '2012-09-01'; This is our first look at SQL timestamps. They're formatted in descending order of magnitude:
YYYY-MM-DD HH:MM:SS.nnnnnn. We can compare them just like we might a unix timestamp, although getting the differences between dates is a little more involved (and powerful!). In this case, we've just specified the date portion of the timestamp. This gets automatically cast by postgres into the full timestamp 2012-09-01 00:00:00.