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 5 5150
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
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)
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marcus |
last post by:
I am having some problems with trying to perform calculations on time
fields. Say I have a start time and an end time, 1:00:00 and 2:30:00
(on a 24 hour scale, not 12). I want to find the...
|
by: Moluske |
last post by:
The web site was on another server i switch to a new one but this server hav
now PHP Safe Mode to ON so i get error when creating thumbnail. I cant put
PHP Safe mode to OFF. is ther any thing i...
|
by: news.microsoft.com |
last post by:
In vb.net, is there anyway to convert a SQL timestamp field into a real
date. Also can I convert a real date into a SQL timestamp
|
by: Mal Ball |
last post by:
I hope I have the right forum for this question. I have an existing Windows
application which uses a SQL Server database and stored procedures. I am now
developing a web application to use the same...
|
by: JJ |
last post by:
How do I set one field to have the updated timestamp, and another to have
the created timestamp?
I want to do this directly from code generated from DB Designer if
possible?!
JJ
|
by: BenG |
last post by:
Hi. I have a gridview control on a web form (asp.net 2.0) that's bound to a
objectDataSource. The objectdatasource which is bound to a class I've
written in the DAL to read and update the database....
|
by: Gert van der Kooij |
last post by:
Hi,
Our SQL Replication is between DB2 databases on Windows servers.
I'm searching for the document which tells me how to migrate our SQL
Replication environment from V8 to V9 (we also need to...
|
by: marc |
last post by:
hi
im trying to convert Date() into a unix timestamp so i can stick the
result into a mysql db, please help!
|
by: Hemant Shah |
last post by:
Folks,
How can I convert date/time/timestamp to an integer?
According to UDB 8.1 docs that I have,
INTEGER('1964-07-20') should return 19640720, but when I run the SQL
statement I get...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |