Postgresql Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Postgresql Dates

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-06-23 : 11:11:35

Dunno if anyone here uses Postregsql? ... but on the off chance:

I need to know how to write a 100% guaranteed unambiguous date constant - the sort of thing we would do in MSSQL as 'yyyymmdd' or 'yyyy-mm-ddThh:mm:ss.sss'

Here's the story so far:

I have to get newer product data from Postgresql, so I do this:


SELECT *
INTO	##TEMP_PRODUCT_TEXT
FROM	OPENQUERY(MyLinkedPostgresqlServer, 
'SELECT *
FROM
(
SELECT
	product_code,
	...
	change_date
FROM	MyProductTable
WHERE	1=1
	AND (
	    change_date > to_date(''2010-05-28 14:13:44.000'', ''yyyy-mm-dd hh:mm:ss.sss'')
	)
ORDER BY change_date
	, product_code
	, ...
) X
LIMIT 5000'
)

The most recently received "change_date" is remembered, so the idea is to resume from that date next time the query runs.

I couldn't get any data, even though I know that there is some.

So I sent this query to Postgresql:


SELECT	to_date('2010-05-28 14:13:44.000', 'yyyy-mm-dd hh:mm:ss.sss') AS MyDate

and got back


mydate     
---------- 
2011-01-28

which is scary beyond belief! but no doubt I have fouled up somewhere.

I trawled the DOCs the first time around to get a bullet-proof way of specifying a date unambiguously which is when I found to_date()

There are examples listed (DATE '2010-05-28') and TIME('14:13:44') and some stuff that says that ISO is unambiguous ... but no examples, that I found, of the 100% correct, unambiguous, guaranteed way to specify a date constant.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-23 : 12:00:59

Maybe it's the driver you're using for the linked server? If it's ODBC it may not support certain features or query structures, like subqueries, etc.

I've used PostGres a little bit but not as a linked server. Are you sure change_date is a date/time data type?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-23 : 12:02:11

You might be able to get better info over at:
http://www.dbforums.com/postgresql/

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-06-23 : 13:42:40

"Maybe it's the driver you're using for the linked server? If it's ODBC it may not support certain features or query structures, like subqueries, etc."

Yeah, its crap. Actually that's why all the queries are nested ... the driver attempts to be helpful and parse the query, and reports back Syntax Errors for pretty much everything we try to do as its outside whatever out-of-date lexicon its holding

But nested sub-queries appear to be pass-through

Client has since tested the


SELECT	to_date('2010-05-28 14:13:44.000', 'yyyy-mm-dd hh:mm:ss.sss') AS MyDate

direct on Postgresql (using Postgresql client tools I presume, they are not an MSSQL shop!) and got the same result

Their recommendation is


SELECT	timestamp '2010-12-31 14:13:44' AS MyDate

which seems to work (and indeed gives error with all the rogue dates I've thrown at it including 29-Feb on non-leap year, but 29-Feb on leap year was allowed etc.)

So I'm going to go with that ... I'm pretty sure I saw it in the DOCs as a proposed solution but being an MS-SQL-Boy I kinda skipped-along when I saw TIMESTAMP and DATE in the same sentence!

If that gives me grief I'll trot off to dbForums - I've had an account there for years (but single digit post-count I think!)

Many thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-23 : 15:01:04

Can you change drivers? What version of PostGres are you connecting to?

Kristen
Test

22859 Posts

Posted - 2010-06-23 : 18:37:04

The client set up the drivers - so easier to stick with what they did, but the drivers clearly aren't that good (lots of basic statements don't get through unless we nest them as a sub query). However, all we want to do is pull data from PostgreSQL with simple queries so I'm happy to live with it - or perhaps I'm missing your point?

Version() gives

PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518

From Control panel : Data sources the driver appears to be:

PostreSQL 64-Bit ODBC Drivers 8.03.04.00 "PostgreSQL Global Development Group" 03-Aug-2009

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-23 : 20:56:22

My suggestion was to try another driver, preferably OLEDB, and see if it works better. Obviously test it in a non-production environment first, if you have the credentials to set up a linked server.

Here's one possible driver:

http://www.pgoledb.com/

Kristen
Test

22859 Posts

Posted - 2010-06-24 : 01:38:47

Thanks Rob. It does seem a bit 1980's to be using an ODBC driver! I will discuss with client. Project is not yet Live, so its a good time to make any changes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-24 : 03:47:00

Why are you converting to dates using to_date?

Try this

change_date > ''2010-05-28 14:13:44.000''

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-24 : 13:58:20

Because to-date was in the DOCs I read and looked to be the only thing that was unambiguous - everything else seemed to me to be just like MS SQL's "Don't worry, we know what a date looks like, we'll guestimate the format you've used, use any date format your like" when what MS SQL means, but doesn't say, is "but be sure you don't change Server settings / Language / Country settings Yada-Yada-Yada ..."

The PostgeSQL DOCs explain that the ISO settings for DATE and TIME are unambiguous, but it only describes them being used separately, but I didn't see anything that said it was safe to use the two together.

I may have seen TIMESTAMP in the DOCs but if I did I expect I ignored it 'coz in MS SQL TIMESTAMP has nothing to do with Dates + Times of course.

Anyways, I used to_date and it appeared to work. I was providing an explicit FORMAT descriptor ... so what could possibly go wrong? If to_date() was not supported or whatever I assumed I would get an error ... I never dreamt I would get some other random date ... two days wasted accusing the client of not providing all the data, head scratching, all sorts - in a project with extremely unreasonable deadlines dates

Pity the deadline dates weren't set in PostgreSQL using to_date() - I'd have another year until anythign was due!

Anyway, rather than


change_date > '2010-05-28 14:13:44.000'

I now think that the correct / reliable / unambiguous Date Constant syntax is:


change_date > timestamp '2010-05-28 14:13:44.000'

</rant>

I don't like that


SELECT 'XXX' AS MyDate

converts the result column name to "mydate" all lower case either

</rant2>

bravo
Starting Member

7 Posts

Posted - 2010-06-24 : 16:05:48

Maybe it's the driver you're using for the linked server? If it's ODBC it may not support certain features or query structures, like subqueries, etc.

Nice Forum

Kristen
Test

22859 Posts

Posted - 2010-06-24 : 20:16:18

The to_date() issue was tested, with the same results, direct on PostgreSQL

The driver supports diddly-squat, but seems to support everything I need if I NEST the query in a bland wrapper-query.

In the outer query not even an "--" comment is supported.

Kristen
Test

22859 Posts

Posted - 2010-06-26 : 16:51:31

"2010-05-28 14:13:44.000 using yyyy-mm-dd hh:mm:ss.sss"

"mm" is month ... I should have put "mi" in the minutes. So it added 13 months to the date from the Minutes instead of the 05 from the month position.

Bit sloppy that it didn't catch this and give me an error IMHO. "mm" twice, and one of the "mm" was "13 months"!

But more sloppy was my date descriptor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources