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
Nov 23 '05
19 4538
problem is that '1 months':: interval does not have the same value if you
add it to a date or another :
=> SELECT '2004-02-01'::timestamp+ '1 month'::interva l,
'2004-03-01'::timestamp+ '1 month'::interva l;
?column? | ?column?
---------------------+---------------------
2004-03-01 00:00:00 | 2004-04-01 00:00:00
SELECT '2004-03-01'::timestamp-'2004-02-01'::timestamp,
'2004-04-01'::timestamp-'2004-03-01'::timestamp;
?column? | ?column?
----------+----------
29 days | 31 days
That's because a month is an undefined number of days (also some years
are 366 days). In that case '1 months':: interval is either 29 or 31 days
but it could be 28 in february 2003 or 30 in april !
Thus if we have a date d and two intervals i1 and i2 :
The comparison (d+i1) < (d+i2) depends on the value of d (and the
timezone).
For instance if i1 is '1 month' and i2 is '30 days', we have :
SELECT '2004-02-01'::timestamp+ '1 month'::interva l,
'2004-02-01'::timestamp+ '30 days'::interval ;
?column? | ?column?
---------------------+---------------------
2004-03-01 00:00:00 | 2004-03-02 00:00:00
Thus (d+i1) < (d+i2)
SELECT '2004-04-01'::timestamp+ '1 month'::interva l,
'2004-04-01'::timestamp+ '30 days'::interval ;
?column? | ?column?
---------------------+---------------------
2004-05-01 00:00:00 | 2004-05-01 00:00:00
Thus (d+i1) = (d+i2)
SELECT '2004-03-01'::timestamp+ '1 month'::interva l,
'2004-03-01'::timestamp+ '30 days'::interval ;
?column? | ?column?
---------------------+---------------------
2004-04-01 00:00:00 | 2004-03-31 00:00:00
Thus (d+i1) > (d+i2)
And that's normal ! Intervals having months are extremely useful to
express the idea of 'same day, next month' that you can't do with just an
interval expressed in seconds. However, beware :
SELECT '2004-01-31'::timestamp+ '1 month'::interva l;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-30'::timestamp+ '1 month'::interva l;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-29'::timestamp+ '1 month'::interva l;
?column?
---------------------
2004-02-29 00:00:00
(1 ligne)
SELECT '2004-01-28'::timestamp+ '1 month'::interva l;
?column?
---------------------
2004-02-28 00:00:00
31 january + 1 month = 29 february (it clips at the end of the month,
which is IMHO GOOD).
How can we sort intervals meaningfully in these conditions ? Can we ? In
fact the value of an interval depends on the application, and intervals
with months are in another 'world' than intervals with only seconds...
same thing for years. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Oct 24, 2004, at 4:13 PM, Pierre-Frédéric Caillaud wrote: How can we sort intervals meaningfully in these conditions ? Can we ? In fact the value of an interval depends on the application, and intervals with months are in another 'world' than intervals with only seconds... same thing for years.
Added to this, I've been wondering whether '1 day'::interval is also
problematic wrt daylight savings time or changing time zones. The whole
thing seems pretty hairy to me.
Michael Glaesemann
grzm myrealbox com
---------------------------(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
Michael Glaesemann <gr**@myrealbox .com> writes: Added to this, I've been wondering whether '1 day'::interval is also problematic wrt daylight savings time or changing time zones.
This is exactly the point I alluded to earlier: intervals need to have
three components (months, days, seconds) not just two. That's been on
the to-do list for quite awhile. All the other units we support for
intervals bear a fixed relationship to one or another of these, so
three is sufficient.
Question to think about : should we allow fractional months or days in
the stored representation? There are some places where the existing
restriction that the months field is an integer requires awkward
compromises. On the other hand, it's not real clear what a fractional
month actually means, and similarly a fractional day is hard to assign
meaning to without positing that 1 day == 24 hours.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Sun, Oct 24, 2004 at 11:29:13 -0400,
Tom Lane <tg*@sss.pgh.pa .us> wrote: Question to think about: should we allow fractional months or days in the stored representation? There are some places where the existing restriction that the months field is an integer requires awkward compromises. On the other hand, it's not real clear what a fractional month actually means, and similarly a fractional day is hard to assign meaning to without positing that 1 day == 24 hours.
There are reasonable addition and subtraction operation definitions
on two intervals. There might be some application where you want to
keep track of fractional months or days. What I am not sure of is
would you really have a reason to add fractional months or days
to a timestamp. There are a couple reasonable definitions you might
make for this definition, but I don't really see a good reason to
want this. ne thing to note, when adding intervals you can add the
fractions normally. When adding to a date you can get the actual length
of the day or month the fractional part adds to, if you wanted to use
that information. (Though the resulting day or month may not be the
one you added the fractional month to.)
I think starting with a type where months and days were integers would be
OK, since you could generalize it to handle fractional months and days
later and not break applications.
Another thing to think about when designing this type, is that when
adding timestamps and intervals it makes a difference in which order
you add the months, days and seconds.
---------------------------(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
Bruno Wolff III <br***@wolff.to > writes: 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.
I like the latter approach, mainly because it gives a defensible
rationale for using a particular exact value. With the solar-year
approach there's no strong reason why you should use 2000 (or any other
particular year) as the reference; and any value you did use would be
subject to both roundoff and observational error. With the Gregorian
calendar as reference, 2629746 seconds is the *exact* answer, and it's
correct because the Pope says so ;-).
(Or, for the Protestants among us, it's correct because the SQL standard
specifies use of the Gregorian calendar.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Tom Lane 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?
(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
As any of us who have ever researched how to calculate time know;
1) The amount of time in 1 year depends on the year due to
leap years.
2) The amount of time in 1 month depends on the month and year
because a month is an arbitrary number of days.
3) A week is a theological creation always equal to 7 days.
Using the Gregorian Calendar there are 10 missing days between
Oct. 4, 1582 and Oct. 15, 1582 . Leap Years are (((every 4 years)
except when modulo 100) except when modulo 400).
It is therefore not possible to define a Month or Year in Seconds,
without knowing which Day, Month and Year you calculating.
Time constants :
1 Solar Day = 23 hours 56 minutes 4.091 seconds
1 Lunar Month = 27.32158 days
1 Tropical Year = 365.24215 Solar Days
1 Year in Gregorian time is :
365 Days 5 Hours 49 Minutes 12 Seconds
As it is now obvious there is not any simple way to convert
months to seconds since a month is an abstract number of days used
to split four (13 week) seasons three ways plus one day every non
leap year and two days every leap year.
When calculating any usage based on time, it is a good idea to
store usage in days:hours:minu tes:seconds because they are static
and stable, if you discount the deceleration of the earth and
corrections in leap seconds for atomic clocks [see http://tycho.usno.navy.mil/leapsec.html ].
Trivia: In approximately 620 million years a day will be twice as
long as it is today.
--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Tom Lane wrote: Bruno Wolff III <br***@wolff.to > writes:
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.
I like the latter approach, mainly because it gives a defensible rationale for using a particular exact value. With the solar-year approach there's no strong reason why you should use 2000 (or any other particular year) as the reference; and any value you did use would be subject to both roundoff and observational error. With the Gregorian calendar as reference, 2629746 seconds is the *exact* answer, and it's correct because the Pope says so ;-).
(Or, for the Protestants among us, it's correct because the SQL standard specifies use of the Gregorian calendar.)
regards, tom lane
Give or take one day every 4000 years. ;-)
--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
---------------------------(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)
Guy Fraser wrote: Trivia: In approximately 620 million years a day will be twice as long as it is today.
Do you think then that Postgres628M.0 will fix it ? :-)
Regards
Gaetano Mendola
Gaetano Mendola wrote: Guy Fraser wrote:
Trivia: In approximately 620 million years a day will be twice as long as it is today.
Do you think then that Postgres628M.0 will fix it ? :-)
Regards Gaetano Mendola
I just hope, I don't have to work an equivalent fraction of the day for the
same pay, but with any luck I'll have all my bills paid and be retired by
then. ;-)
--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Wed, Oct 27, 2004 at 16:26:13 -0600,
Guy Fraser <gu*@incentre.n et> wrote: When calculating any usage based on time, it is a good idea to store usage in days:hours:minu tes:seconds because they are static and stable, if you discount the deceleration of the earth and corrections in leap seconds for atomic clocks [see http://tycho.usno.navy.mil/leapsec.html ].
The length of calendar days isn't constant. In many timezones, one day a year
is 23 hours long and another is 25 hours long.
Having month and year intervals is useful for events that repeat monthly or
yearly in spite of there not being a constant number of seconds between
events.
---------------------------(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) 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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: 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: 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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |