1

Given that I have a checkins table in postgresql w/ a created_at column, how would I go about selecting all of the records that were created on a given date (2012-10-17 for example) while considering the timezone 'America/New_York' (and taking into account daylight savings time)?

Currently I'm executing the following query in psql (PostgreSQL) 9.1.6 but it seems to be missing some records that were added towards the end of the day.

SELECT COUNT(*) AS count FROM checkins WHERE date(created_at) = '2012-10-17';

Here is a listing of all of the records for the given day. The query above is returning 10 which is incorrect. I should be getting back 13.

  id  | waiver_id |         created_at         |         updated_at         
------+-----------+----------------------------+----------------------------
 1391 |         1 | 2012-10-18 00:42:07.308453 | 2012-10-18 00:42:07.308453
 1390 |       286 | 2012-10-18 00:38:53.102685 | 2012-10-18 00:38:53.102685
 1389 |       590 | 2012-10-18 00:38:28.811605 | 2012-10-18 00:38:28.811605
 1388 |         7 | 2012-10-17 22:29:25.610774 | 2012-10-17 22:29:25.610774
 1387 |      1155 | 2012-10-17 22:01:40.647219 | 2012-10-17 22:01:40.647219
 1386 |      1154 | 2012-10-17 22:00:05.477698 | 2012-10-17 22:00:05.477698
 1385 |      1153 | 2012-10-17 21:57:59.698076 | 2012-10-17 21:57:59.698076
 1384 |      1152 | 2012-10-17 21:08:32.533203 | 2012-10-17 21:08:32.533203
 1383 |      1151 | 2012-10-17 21:07:42.500036 | 2012-10-17 21:07:42.500036
 1382 |       115 | 2012-10-17 21:04:03.075333 | 2012-10-17 21:04:03.075333
 1381 |         3 | 2012-10-17 20:30:13.741647 | 2012-10-17 20:30:13.741647
 1380 |       245 | 2012-10-17 20:29:53.808852 | 2012-10-17 20:29:53.808852
 1379 |      1150 | 2012-10-17 20:27:30.057269 | 2012-10-17 20:27:30.057269

checkins columns & data_types

 column_name |          data_type          
-------------+-----------------------------
 updated_at  | timestamp without time zone
 created_at  | timestamp without time zone
 waiver_id   | integer
 id          | integer
Kyle Decot
  • 20,715
  • 39
  • 142
  • 263

1 Answers1

5

Depending what you want exactly, the AT TIME ZONE construct can help.

If your column is of type timestamp [without time zone] and your local time is set to a different time zone than EST, but you want the according dates at EST, this would be your query:

SELECT count(*) AS ct
FROM   checkins
WHERE  (created_at AT TIME ZONE 'EST')::date = '2012-10-16';

Can't say for sure, your question is missing basic information and leaves room for interpretation.
Comprehensive explanation of PostgreSQL's time zone handling with examples and links at this related answer.

If you want daylight saving time covered automatically, use a time zone name instead of a time zone abbreviation. Like:

created_at AT TIME ZONE 'America/New_York'

More info about that in this related question.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I've tried this but I still get back 10 results instead of 13. Any ideas? – Kyle Decot Oct 18 '12 at 12:14
  • Also, what basic information is my question missing? I can provide you w/ whatever you need – Kyle Decot Oct 18 '12 at 14:41
  • @KyleDecot: The following would help: *exact* data type of `created_at`; "while considering the time zone" does not clarify to which time zone the date should be applicable; your local time zone (time zone name) where you execute the query; the current time zone where you display the example data (is the same?); do you want to consider DST? Your version of PostgreSQL. – Erwin Brandstetter Oct 18 '12 at 17:02
  • I just noticed something odd. If I do `SELECT count(*) AS ct FROM checkins WHERE (created_at AT TIME ZONE 'GMT')::date = '2012-10-17';` I get back 13 which is the correct number. That seems weird to me. Am I just confused as to how time zones work in postgres? – Kyle Decot Oct 18 '12 at 17:27
  • @KyleDecot: What do you get when you run `SHOW timezone`? And what is the time zone your database *server* is configured to? (The server setting is used when running with `localtime`.) – Erwin Brandstetter Oct 18 '12 at 21:31