1

I have a date field in a postgresql database as "TIMESTAMP WITHOUT TIMEZONE".

It gets stored as: 2016-02-03 00:00:00 for an event that happens today (Wed 3rd Feb 2016). I want to return all events from today up until two weeks in the future.

I set a date two weeks from now:

var twoWeeksFromNow = new Date(+new Date + 12096e5);

If I create today's date as so:

var today = new Date().setHours(0,0,0);

I get a timestamp out of range error.

If I set it as 

var today = new Date(new Date().setHours(0,0,0));

It just doesn't return today's events.

My query looks like:

"SELECT * FROM events WHERE event_date >= today AND event_date < twoWeeksFromNow"

How do I get it to match today's date with a javascript date object or stringified date object? Where am I going wrong?

Cœur
  • 37,241
  • 25
  • 195
  • 267
OliverJ90
  • 1,291
  • 2
  • 21
  • 42
  • [*setHours*](http://www.ecma-international.org/ecma-262/6.0/#sec-date.prototype.sethours) returns the time value of the modified Date object, not a Date object or string. – RobG Feb 03 '16 at 22:34

2 Answers2

1

You must be aware that "today" is defined by your current timezone setting. If all your operations are confined to the same time zone, it's fine to use timestamp [without time zone]. Else, consider timestamp with time zone - and define "today" more closely.

Next, you shouldn't call a timestamp column event_date because it's not a date. A date does not include a time component.

Defining "from today up until two weeks in the future" to be a period of exactly 14 days (so if it's Friday today, it ends on Thursday):

SELECT *
FROM   events
WHERE  event_date >= current_date  -- work with time zone session
AND    event_date <  current_date + 14;

  • The timezone setting of your current session defines "today".

  • Don't cast the column to date like another answer suggests event_date::date or the expression is not sargable any more and you can't use a plain index on (event_date) - which is the key to performance in bigger tables.

  • You can just add integer to a date (to add days). The same is not possible with timestamps. The date is converted to timestamp implicitly, assuming time 00:00 in the process.

If you want to make sure that "today" is not shifted to some other time zone:

SELECT *
FROM   events, date_trunc('day', now() AT TIME ZONE 'Europe/Vienna') AS t
WHERE  event_date >= t
AND    event_date <  t + interval '14 days';

This is guaranteed to return correct results independent of the current time zone setting.

Replace 'Europe/Vienna' with your time zone name. Use a time zone name (not a time zone abbreviation or a numeric offset) to defend against DST (daylight saving time) nonsense. You can find them all in the system table pg_timezone_names.

Detailed explanation:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

This may be a solution for your problem. 

    SELECT * FROM events WHERE event_date ::date >= CURRENT_DATE  AND event_date ::date < CURRENT_DATE +14
Mesbah Gueffaf
  • 518
  • 1
  • 7
  • 21