Postgresql – Compare a date and a timestamp with time zone with now() in the same query

datatypesdate formatpostgresqltimestamp

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other.

When I try and query the server with timestamp with time zone by casting the timestamp as a date:

...
WHERE
    (
        status_code = '30000'
        OR status_code = '30005'
    )
AND CAST(expiration AS DATE) > now()

It works, but using the same query on the servers where expiration is already a date fails:

[Err] ERROR: invalid input syntax for type date: "No End Date"

Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.

Best Answer

This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration > now()

PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).

The error message tells a different story. You are actually trying to input a date with invalid syntax.

I wrote a detailed answer about handling of timestamps with or without time zone in PostgreSQL recently - if that should the issue:

Ignoring timezones altogether in Rails and PostgreSQL

As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:

...
AND   expiration::timestamptz > now()

If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially in a CASE construct:

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END > now()

The manual about the special value infinity.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

If the format of the time stamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:

to_date('07/08/2013', 'DD/MM/YYYY')

How to convert “string” to “timestamp without time zone”

Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE 'UTC' -- desired time zone here

If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

How to Convert Timestamp/Rowversion to Date and Time in SQL Server

It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.

The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.

But here is a work-around to implement the ModifiedAt, UpdatedDate column type. This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.

But you need to do a bit of work;

1. Create a table "UpdateTimeStamp" with three columns

(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:

Create Table dbo.UpdateTimeStamp(
    OldRowVersion binary(8) not null, 
    CreatedDate SmallDateTime not null constraint DF_UpdateTimeStamp_CreatedDate Default getdate(),
    NewRowVersion ROWVERSION not null CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
)

Insert the first row manually

Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate) 
VALUES (0x0000000000000000, '2000-01-01')

2. Create a SQL Agent job

Create a standard SQL Agent job which inserts one row in the table every one minute.

Make step 1 run this code:

Insert into dbo.UpdateTimeStamp(OldRowVersion) 
SELECT TOP (1) NewRowVersion 
FROM dbo.UpdateTimeStamp 
ORDER BY NewRowVersion DESC

Set the schedule to run every 1 minute.

3. Query the data

You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:

SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate 
FROM dim.MyTable MT 
LEFT JOIN dbo.UpdateTimeStamp uts 
    ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate

Caveat, buts...

1) Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.

2) This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively. Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.

Bonus features

The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts. So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions. Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.