469,953 Members | 1,998 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,953 developers. It's quick & easy.

Problem in converting int to timestamp value - why?

Hello,

I've an integer column in a certain table that I need to convert into a
timestamp value to finally get a day difference to the current date.
From the manual it looks like straight forward, but the following line

is a great mistery for me:

SELECT to_timestamp(to_char(20041217, '9999 99 99'), 'YYYY MM DD')

results in a timestamp "2171-11-05 22:00:00" ??? The int-to-char
conversion works well ("2004 12 17") but the to_timestamp() doesn't work
as expected - why?

I'm running Postgresql 7.4
Thanks a lot,
Soeren Gerlach
-------------------------------------------------------------
Heute schon gelacht? http://all-about-shift.com/dailystrips/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
5 5029
On Sun, Sep 19, 2004 at 04:17:52PM +0200, Soeren Gerlach wrote:
I've an integer column in a certain table that I need to convert into a
timestamp value to finally get a day difference to the current date.
From the manual it looks like straight forward, but the following line

is a great mistery for me:

SELECT to_timestamp(to_char(20041217, '9999 99 99'), 'YYYY MM DD')

results in a timestamp "2171-11-05 22:00:00" ??? The int-to-char
conversion works well ("2004 12 17") but the to_timestamp() doesn't work
as expected - why?


to_char() is returning a leading space:

test=> SELECT 'x' || to_char(20041217, '9999 99 99') || 'y';
?column?
---------------
x 2004 12 17y

The leading space is confusing to_timestamp():

test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
to_timestamp
---------------------
2171-11-06 06:00:00

You can tell to_timestamp() to account for the leading space or you
can tell to_char() to suppress it:

test=> SELECT to_timestamp(to_char(20041217, '9999 99 99'), ' YYYY MM DD');
to_timestamp
------------------------
2004-12-17 00:00:00-07

test=> SELECT to_timestamp(to_char(20041217, 'FM9999 99 99'), 'YYYY MM DD');
to_timestamp
------------------------
2004-12-17 00:00:00-07

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Mike,

thanks for pointing me to the "Template Pattern Modifier" ,-)) I've just
found it in the doc with your help and it works now perfectly.
Thanks,
Soeren

On Sun, Sep 19, 2004 at 04:17:52PM +0200, Soeren Gerlach wrote:
I've an integer column in a certain table that I need to convert into a
timestamp value to finally get a day difference to the current date.
From the manual it looks like straight forward, but the following line

is a great mistery for me:

SELECT to_timestamp(to_char(20041217, '9999 99 99'), 'YYYY MM DD')

results in a timestamp "2171-11-05 22:00:00" ??? The int-to-char
conversion works well ("2004 12 17") but the to_timestamp() doesn't work
as expected - why?


to_char() is returning a leading space:

test=> SELECT 'x' || to_char(20041217, '9999 99 99') || 'y';
?column?
---------------
x 2004 12 17y

The leading space is confusing to_timestamp():

test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
to_timestamp
---------------------
2171-11-06 06:00:00

You can tell to_timestamp() to account for the leading space or you
can tell to_char() to suppress it:

test=> SELECT to_timestamp(to_char(20041217, '9999 99 99'), ' YYYY MM DD');
to_timestamp
------------------------
2004-12-17 00:00:00-07

test=> SELECT to_timestamp(to_char(20041217, 'FM9999 99 99'), 'YYYY MM DD');
to_timestamp
------------------------
2004-12-17 00:00:00-07


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3
Michael Fuhr <mi**@fuhr.org> writes:
The leading space is confusing to_timestamp(): test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
to_timestamp
---------------------
2171-11-06 06:00:00


I'd still say this is a bug. If to_timestamp can't match the input to
the pattern, it should throw an error, not silently return garbage.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4
On Sun, Sep 19, 2004 at 12:31:26PM -0400, Tom Lane wrote:
Michael Fuhr <mi**@fuhr.org> writes:
The leading space is confusing to_timestamp():

test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
to_timestamp
---------------------
2171-11-06 06:00:00


I'd still say this is a bug. If to_timestamp can't match the input to
the pattern, it should throw an error, not silently return garbage.


Agreed that it's a bug. Returning garbage violates the Principle
of Least Surprise.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5
On Sun, 2004-09-19 at 16:09 -0600, Michael Fuhr wrote:
On Sun, Sep 19, 2004 at 12:31:26PM -0400, Tom Lane wrote:
Michael Fuhr <mi**@fuhr.org> writes:
The leading space is confusing to_timestamp():

test=> SELECT to_timestamp(' 2004 12 17', 'YYYY MM DD');
to_timestamp
---------------------
2171-11-06 06:00:00


I'd still say this is a bug. If to_timestamp can't match the input to
the pattern, it should throw an error, not silently return garbage.


I already start work on new to_char() that will more paranoid, for now
is enough for right usage follow docs.

Karel

--
Karel Zak
http://home.zf.jcu.cz/~zakkr/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Marcus | last post: by
reply views Thread by Moluske | last post: by
1 post views Thread by news.microsoft.com | last post: by
7 posts views Thread by JJ | last post: by
3 posts views Thread by Gert van der Kooij | last post: by
5 posts views Thread by Hemant Shah | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.