Hello everyone:
I'm a PostgreSQL newbie, working now with dates, times, timestamps and
intervals.
I have three questions about the above:
FIRST:
--------
I have observed that, for PostgreSQL, one year is actually 360 days:
SELECT '1 year'::timestam p = '360 days'::timestam p;
?column?
-------------
t
Glubs! I believed that 1 year is 365 days, or 366 if leap. Is it normal?
SECOND:
-----------
When I want to check how many time is between two dates, I have two options
(which shows two different results):
SELECT '30-09-04'::timestamp - '30-09-03'::timestamp,
age('30-09-04'::timestamp, '30-09-03'::timestamp) ;
?column? | age
-------------------------------
@ 366 days | @ 1 year
The results are different. If we compare the two results:
SELECT ('30-09-04'::timestamp - '30-09-03'::timestamp) =
age('30-09-04'::timestamp, '30-09-03'::timestamp) ;
?column?
--------------
f
Obviously, it returns False, because I told in the first question, 1 year is
360 days for PostgreSQL.
The question is: is it normal? Which of the two methods is the correct? To
substract timestamps? Or to use the age function?
THIRD:
--------
As I told in the second question, when I do:
SELECT '30-09-04'::timestamp - '30-09-03'::timestamp;
the result is:
?column?
--------------
@ 366 days
The question is: is there any way to "normalize" the result, such that the
result was:
@ 1 year 1 day
?
I think it's better (and more correct) "@ 1 year 1 day" rather than "@ 366
days". Is there any way to achieve that?
Thanks to all.
Ricardo.
_______________ _______________ _______________ _______________ _____
Horóscopo, tarot, numerología... Escucha lo que te dicen los astros. http://astrocentro.msn.es/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org 19  4537 
On Fri, Oct 22, 2004 at 13:37:19 +0200,
Ricardo Perez Lopez <ri*****@hotmai l.com> wrote: Hello everyone:
I'm a PostgreSQL newbie, working now with dates, times, timestamps and
intervals.
I have three questions about the above:
FIRST:
--------
I have observed that, for PostgreSQL, one year is actually 360 days:
No it isn't. The interval is stored as months and seconds. When
adding intervals to timestamps, adding months and adding seconds are
handled differently. Under some circumstances the months part gets
converted to seconds, and in that event a month is taken to be as
long as 30 days.
SELECT '1 year'::timestam p = '360 days'::timestam p;
?column?
-------------
t
Glubs! I believed that 1 year is 365 days, or 366 if leap. Is it normal?
SECOND:
-----------
When I want to check how many time is between two dates, I have two options
(which shows two different results):
SELECT '30-09-04'::timestamp - '30-09-03'::timestamp,
age('30-09-04'::timestamp, '30-09-03'::timestamp) ;
?column? | age
-------------------------------
@ 366 days | @ 1 year
The results are different. If we compare the two results:
SELECT ('30-09-04'::timestamp - '30-09-03'::timestamp) =
age('30-09-04'::timestamp, '30-09-03'::timestamp) ;
?column?
--------------
f
Obviously, it returns False, because I told in the first question, 1 year
is 360 days for PostgreSQL.
That isn't really why. When you use age you get an interval with a mix of month
and seconds parts. If you subtract two timestamps then you get an interval
with just a seconds part.
The question is: is it normal? Which of the two methods is the correct? To
substract timestamps? Or to use the age function?
THIRD:
--------
As I told in the second question, when I do:
SELECT '30-09-04'::timestamp - '30-09-03'::timestamp;
the result is:
?column?
--------------
@ 366 days
The question is: is there any way to "normalize" the result, such that the
result was:
@ 1 year 1 day
Use age. If you do that, that is what the interval will look like internally.
I don't think there is an easy way to output the value of an interval
so that it looks like that. But if you use it in operations it should
do what you want. (Though you need to consider whether you want the
day added before or after you add the 12 months.)
?
I think it's better (and more correct) "@ 1 year 1 day" rather than "@ 366
days". Is there any way to achieve that?
Thanks to all.
Ricardo.
_______________ _______________ _______________ _______________ _____
Horóscopo, tarot, numerología... Escucha lo que te dicen los astros.
http://astrocentro.msn.es/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
"Ricardo Perez Lopez" <ri*****@hotmai l.com> writes: I have observed that, for PostgreSQL, one year is actually 360 days:
SELECT '1 year'::timestam p = '360 days'::timestam p;
?column?
-------------
t
Nonsense.
regression=# SELECT '1 year'::timestam p = '360 days'::timestam p;
ERROR: invalid input syntax for type timestamp: "1 year"
How about telling us what you *really* did, instead of posting faked
examples?
There are some contexts in which an interval (not a timestamp) of 1
month will be taken as equivalent to 30 days, for lack of any better
idea, but it's not the case that Postgres doesn't know the difference.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Tom Lane <tg*@sss.pgh.pa .us> writes: "Ricardo Perez Lopez" <ri*****@hotmai l.com> writes: I have observed that, for PostgreSQL, one year is actually 360 days:
SELECT '1 year'::timestam p = '360 days'::timestam p;
?column?
-------------
t
Nonsense.
regression=# SELECT '1 year'::timestam p = '360 days'::timestam p;
ERROR: invalid input syntax for type timestamp: "1 year"
How about telling us what you *really* did, instead of posting faked
examples?
FWIW:
template1=# select '1 year'::interval = '360 days'::interval ;
?column?
----------
t
(1 row)
template1=# select '1 year'::interval = '365 days'::interval ;
?column?
----------
f
(1 row)
template1=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
-Doug
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Doug McNaught <do**@mcnaught. org> writes: template1=# select '1 year'::interval = '360 days'::interval ;
?column?
----------
t
(1 row)
Yeah, if you look at interval_cmp_in ternal() it's fairly obvious why.
I think that this definition is probably bogus, and that only intervals
that match exactly (equal months parts *and* equal seconds parts) should
be considered "equal". However the most obvious way to redefine it
(compare the months, and only if equal compare the seconds) would lead
to rather nonintuitive behaviors such as "'1 year' > '1000 days'".
Anybody have any thoughts about a better way to map the multicomponent
reality into a one-dimensional sorting order?
(Note also that as Bruno was just mentioning, we really ought to have
months/days/seconds components, not just months/seconds; which makes the
comparison issue even more interesting.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
On Sat, Oct 23, 2004 at 21:38:15 -0400,
Tom Lane <tg*@sss.pgh.pa .us> wrote: Doug McNaught <do**@mcnaught. org> writes: template1=# select '1 year'::interval = '360 days'::interval ;
?column?
----------
t
(1 row)
Yeah, if you look at interval_cmp_in ternal() it's fairly obvious why.
I think that this definition is probably bogus, and that only intervals
that match exactly (equal months parts *and* equal seconds parts) should
be considered "equal". However the most obvious way to redefine it
(compare the months, and only if equal compare the seconds) would lead
to rather nonintuitive behaviors such as "'1 year' > '1000 days'".
Anybody have any thoughts about a better way to map the multicomponent
reality into a one-dimensional sorting order?
You could return NULL for cases where the number of months in the
first interval is less than the second, but the number of seconds in
the second interval is greater than the first.
You could even tighten things down more by using that months have to
be at least 28 days, but not more than 31 days (neglecting daylight
savings time).
If you want to be able to use a btree index, you need a total ordering, so
in that case I think you have to have things work pretty much the way they do
now, including the way the equality operator works.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Bruno Wolff III <br***@wolff.to > writes: Tom Lane <tg*@sss.pgh.pa .us> wrote: Anybody have any thoughts about a better way to map the multicomponent
reality into a one-dimensional sorting order?
You could return NULL for cases where the number of months in the
first interval is less than the second, but the number of seconds in
the second interval is greater than the first.
No, you can't, at least not if you want to have btree indexes on
interval columns. The comparison operators can never return NULL
for nonnull inputs.
If you want to be able to use a btree index, you need a total ordering, so
in that case I think you have to have things work pretty much the way they do
now, including the way the equality operator works.
We don't have to have this particular sorting decision, we just have
to have *some* unique sorting order. In particular, if we want to say
that two interval values are not equal, we have to be able to say which
one is less. For instance, "compare the months first and only if equal
compare the seconds" would work fine from the point of view of btree.
It's just that that leads to a sort order that users will probably not
like very much.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
On Sat, Oct 23, 2004 at 23:36:05 -0400,
Tom Lane <tg*@sss.pgh.pa .us> wrote:
We don't have to have this particular sorting decision, we just have
to have *some* unique sorting order. In particular, if we want to say
that two interval values are not equal, we have to be able to say which
one is less. For instance, "compare the months first and only if equal
compare the seconds" would work fine from the point of view of btree.
It's just that that leads to a sort order that users will probably not
like very much.
One way to do comparisons is to use a mapping f(m,s) => R and compare
(m1,s1) and (m2,s2) by comparing f(m1,s1) and f(m2,s2) and break ties
by comparing say m1 and m2. This will work as long as f(m,s1) = f(m,s2)
implies s1 = s2. It will probably be desirable to use a subset of these
mappings where f(m,s) = g(m) + h(s). In fact the current system uses
this with g(m) = 30*24*60*60*m and h(s) = s (but without the tiebreak
that compares m values). Because of the way intervals work, I think
you want to use an ordering generated like that you want to use
something of the form f(m,s) = C1*m + C2*s. I also think that treating
a month as 30 days and having round numbers is better than using
something like 1/12 a solar year in seconds. So I think the best plan
is to do things as they are now, except for adding a tie breaker just
using months or seconds for when both intervals give the same number of
seconds when treating months as 30 days, but have a different number of
months.
---------------------------(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 Sat, Oct 23, 2004 at 23:15:57 -0500,
Bruno Wolff III <br***@wolff.to > wrote: by comparing say m1 and m2. This will work as long as f(m,s1) = f(m,s2)
implies s1 = s2. It will probably be desirable to use a subset of these
mappings where f(m,s) = g(m) + h(s). In fact the current system uses
this with g(m) = 30*24*60*60*m and h(s) = s (but without the tiebreak
that compares m values). Because of the way intervals work, I think
you want to use an ordering generated like that you want to use
something of the form f(m,s) = C1*m + C2*s. I also think that treating
a month as 30 days and having round numbers is better than using
something like 1/12 a solar year in seconds. So I think the best plan
is to do things as they are now, except for adding a tie breaker just
using months or seconds for when both intervals give the same number of
seconds when treating months as 30 days, but have a different number of
months.
Some more comments on this. I was thinking about it a bit more and using
1/12 of the number of seconds in a solar year doesn't seem that bad
for comparisons. That way 366 days > 1 year > 365 days. However, if you
go that route, I think you would also want to change EXTRACT so that
when you extract the EPOCH you use the same function as for comparison.
One value I found for a solar year is 365 days, 5 hours, 48 minutes, 45.51
seconds.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Sat, Oct 23, 2004 at 23:51:20 -0500,
Bruno Wolff III <br***@wolff.to > wrote: One value I found for a solar year is 365 days, 5 hours, 48 minutes, 45.51
seconds.
Wikipedia gives 365.242189670 days (86400 seconds) as the length of the
mean solar year in 2000. To give you some idea of how constant that values is,
Wikipedia claims that 2000 years ago the mean solar year was about 10 seconds
longer.
Using the above value I get there is an average of 2629743 seconds in a month.
And yet another option is to note that in the Gregorian calendar there are
400*365+97 days or 400*12 months in 400 years, which gives 2629746 seconds
per month on average.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: John Hunter |
last post by:
>>> from datetime import date
>>> dt = date(1005,1,1)
>>> print dt.strftime('%Y')
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ValueError: year=1005 is before 1900; the datetime strftime() methods require year >= 1900
Does anyone know of a datetime string formatter that can handles
strftime format strings over the full range that datetime objects
support?
|
by: Chris |
last post by:
Is there a way to make python create a list of Mondays for a given year?
For example,
mondays =
|
by: Douglas |
last post by:
I have a Vehicle MOT field in my table which i have as a Date field
I dont really want to hold the year, just 'dd mmm' as MOTs are the
same date every year.
I have the field on my form as a listbox holding all the dates of the
year, except 29 Feb which i dont need anyway.
When i select a value from the list i.e. 14 May and it stores it in
the table i believe it stores the current year as well i.e 14 May 2004
The problem now is how do i...
|
by: Martin Emanuelsson |
last post by:
Hello,
Knowing year and weeknumber of this year, I'm looking for some way to get
the date of the first and last day of that particular week.
Does anyone have an idea about how to do this?
Best regards
Martin
|
by: Kasrav |
last post by:
I have another problem hopeful am not bothering you guys too much if u can help that would be wonderful. I have this code here
def year2():
print'This program validates days and months of the year'
day = validate('Enter dayNumber: ',1,31);
month = validate('Enter monthNumber: ',1,12);
while(True):
if (month == 1 or month == 3 or month == 5 or month == 7 or month == 8 or month == 10 or month == 12 and day...
| | |
by: O.B. |
last post by:
Does C# provide an operation for converting year and day-of-the-year
(1-365) to a DateTime object?
|
by: mazwolfe |
last post by:
I'm new here, so excuse me if my style is incorrect. Can anyone come
up with a better method for this calculation?
Code:
int is_leap(int year)
{
switch (year % 19) {
case 0: case 3: case 6: case 8:
case 11: case 14: case 17: return 1;
default: return 0;
|
by: Tommy Jakobsen |
last post by:
Hi.
Is there a method in .NET that takes "year" as an argument and returns the total
number of weeks in that year? For culture da-DK (Danish).
Thanks in advance.
Tommy.
|
by: =?Utf-8?B?cm9kY2hhcg==?= |
last post by:
hey all,
is there a way if you are given a Year and a Week an easy way to go back say
26 weeks ago from given year/week.
for example,
given: 2008/16
26 weeks prior is:
2007/43
thanks,
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
