Search code examples
javascriptpostgresqldatedatetime

PostgreSQL field timestamp without timezone - how to check if it equals today

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?

Solution

  • "Today" is defined by the timezone setting of the executing session. 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.

    Don't name a timestamp column event_date. 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  -- use time zone of 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 and the query can't use a plain index on (event_date), which is the key to performance with bigger tables.

    • You can just add integer to a date (to add days). The input value must be date for this, not a timestamp type. The resulting date is then coerced to timestamp in the filter expression, assuming time 00:00 in the process.

    To ensure that "today" is not shifted to some other time zone:

    SELECT *
FROM   events, date_trunc('day', now(), 'UTC') AS t
WHERE  event_date >= t
AND    event_date <  t + interval '14 days';

    date_trunc('day', now(), 'UTC') requires Postgres 12 or later. Fall back to date_trunc('day', now() AT TIME ZONE 'UTC')in older versions.

    This removes any dependency on current timezone setting.

    Replace 'UTC' 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. Find available names in the system table pg_timezone_names.

    Detailed explanation: