Re: [GENERAL] date with month and year
On Fri, May 22, 2015 at 09:46:10AM +0100, Tim Clarke wrote: On 22/05/15 09:40, Alban Hertroys wrote: On 21 May 2015 at 23:42, Karsten Hilbert karsten.hilb...@gmx.net wrote: You are right in the following aspect: - client sends in NOW at HERE - server knows HERE = UTC+2 And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or +3 Thankfully, those things don't shift as fast as they sometimes do in the movies. But every spring and autumn we do have daylight savings. How does that work with time-zoney-woney timey-wimey calculations! When we schedule a meeting beyond the next DST shift we conventionally know that to mean the meeting will be at 2pm local time THEN, whichever DST is in effect when THEN has come. After all, the very idea of DST is to make 2pm a sensible meeting start time regardless of floating daylight (whether that actually works is another matter :-) Now, with non-DST arbitrary shifts we don't know as much. It *could* be that 2pm suddenly falls into the middle of darkness. At which point meetings better be re-scheduled. And that's the answer to that: it's an application problem, meetings need to be _re-scheduled_ which is NOT PostgreSQL's job. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 22/05/15 09:40, Alban Hertroys wrote: On 21 May 2015 at 23:42, Karsten Hilbert karsten.hilb...@gmx.net wrote: You are right in the following aspect: - client sends in NOW at HERE - server knows HERE = UTC+2 And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or +3 Thankfully, those things don't shift as fast as they sometimes do in the movies. But every spring and autumn we do have daylight savings. How does that work with time-zoney-woney timey-wimey calculations! Tim Clarke -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 21 May 2015 at 23:42, Karsten Hilbert karsten.hilb...@gmx.net wrote: You are right in the following aspect: - client sends in NOW at HERE - server knows HERE = UTC+2 And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or +3 Thankfully, those things don't shift as fast as they sometimes do in the movies. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 22/05/2015 06:09, Adrian Klaver wrote: On 05/21/2015 09:04 PM, Alvaro Herrera wrote: Adrian Klaver wrote: SELECT extract ( YEAR FROM school_day ) AS YEAR, Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). FWIW I think this indenting of FROM inside an extract() call is odd and ugly --- probably just an accident resulting from dealing with the regular FROM clause. It seems to me that the YEAR FROM school_day part should be considered a single argument instead of breaking it in multiple lines. Probably so, but the output is a lot cleaner then what I did in psql. The author of pgFormatter will be interested in your comments: https://github.com/darold/pgFormatter Indentation of FROM clause inside some pg functions ( extract overlay substring trim ) will be processed just as a comma (commit 402304b), like follow: SELECT extract ( year FROM school_day ) AS year; SELECT substring ( firstname FROM 1 FOR 10 ) AS sname; SELECT substr ( firstname, 1, 10 ) AS strpart FROM mytable; Best regards, -- Gilles GPL tools at http://www.darold.net/ (squidclamav - sendmailanalyzer - ora2pg - modproxyhtml - pgCluu squidguardmgr - sysusage - squidanalyzer - pgbadger - pgformatter) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
Sorry, forgot to told you what I'm trying, I have climate data and want to obtain mean temperature and total precipitation and that sort of things per month and year. Think date_trunc is a good solution, but any other advice would be very welcome. (I need to read more about time zones, I'm new at using postgresql) Thank you, Daniel 2015-05-21 12:45 GMT-05:00 Paul Jungwirth p...@illuminatedcomputing.com: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Are there any articles you'd recommend? I'm fond of normalizing all times to UTC and only presenting them in a time zone when I know the current perspective. I've written about that approach in a Rails context here: http://illuminatedcomputing.com/posts/2014/04/timezones/ I find that this helps me to ignore time zones in most parts of my application and cut down on my timezone-related bugs. Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgresql.org/docs/current/static/datatype-datetime.html Now go back and read it again and experiment a while until it makes sense. As Adrian Klaver so eloquently put it, If I have learned anything about dealing with dates and times, is that it is a set of exceptions bound together by a few rules. Every time you think you have the little rascals cornered, one gets away. This is also a very good reason to avoid reinventing the wheel. When you need a break, watch this: https://www.youtube.com/watch?v=-5wpm-gesOY His conclusion is a good one: be very happy that someone else has done the dirty work for you. The Ruby article does make one good point which is that we are talking about what they call an instant or what I like to refer to as a point in time. The point in time is actually a better way of thinking of timestamp with time zone since the timestamp with time zone does not actually store any timezone information - it stores a point in time that can be manipulated in the time-zone of your choosing whereas timestamp without time zone is not a point in time and must be combined with other information to do proper manipulation. The article does also display a couple attitudes that I feel are especially rampant in the web-development community. The first is that web developers shouldn't become educated about the capabilities of a database but rather use the database as a dumb data-store and redo everything themselves (often this includes an utter failure to use the data-integrity capabilities of the database). The second is the assumption that they are the only users of the database and that nobody will ever access the data except through their custom-written Ruby/PHP/Perl/Python code and that no other programming language will ever be used. Woe be to the poor slob who has to deal with ad-hoc queries, analytics platforms or reporting systems that weren't so brilliantly reinvented or who wants to use range-types or other nice PostgreSQL features. Internally PostgreSQL stores timestamp without time zone in UTC but that is entirely irrelevant. What is relevant is that you can provide an instant/point in time in whatever time-zone representation you want and get it back the same way. Want to use a Unix epoch in your code. Go ahead: extract(epoch from yourtstzcol) abstime(yourepochint) Want to assume everything is UTC? No problem: Konsole output set timezone to 'UTC'; Then you can reinvent wheels to your heart's content without wrecking the ability to easily use other tools. By the way, use full timezone names to avoid ambiguity. I don't know what Ruby cooked up but PostgreSQL uses industry-standard names: Konsole output select * from pg_timezone_names; Your original question had to do with month/year. You will have to define this for your use-case but beware that it won't necessarily get you away from time-zone issues as the month ticks over on a zone-by-zone basis. Also note that time-intervals can be a source of interesting side-effects. Operator precedence is important. For example, what is one month? 28-days? 29? 30? 31? Every system must make a judgment call. Add a month to January 31 and you will get February 28. But add/subtract a month from February 28 and you get January 28/March 28. So you can create a query that takes a date, adds a month and subtracts a month and results in a different date. There is nothing to do here but to read the docs and try things. There are similar issues when crossing DST boundaries. If I want to push something out a day in my time-zone on the day that DST changes I can do it easily and understand that PostgreSQL will handle the extra/missing hour. Or I can use an explicit increment of '24 hours' if that is what I want. No extra steps of converting the timestamp without time zone to UTC, converting that to the desired local zone, doing the calculations, converting back to UTC and back to timezone without timestamp all the while potentially adding an easy error such as doing things in the wrong order and checking for DST changeover in the wrong time-zone. Cheers, Steve
Re: [GENERAL] date with month and year
Brian Dunavant wrote on 21.05.2015 21:51: It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE would actually save your bacon. Postgres does not store the time zone. When storing a timestamp with time zone, it is normalized to UTC based on the timezone of the client. When you retrieve it, it is adjusted to the time zone of the client. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
Anyway, I agree that you have to store the time zone *somewhere*, and I suppose that's the reason Joshua remarked that you really shouldn't use WITHOUT TIME ZONE. And often a time has one perspective that is canonical or preferred, e.g. the time zone of the user who created the object. And in that case WITH TIME ZONE gives you a convenient place to store that. No it doesn't. WITH TIME ZONE simply means that whatever the user sends to the database is meant to represent that time zone. As far as PostgreSQL is concerned it will convert that to UTC, store UTC AND THROW AWAY THE TIMEZONE INFORMATION While that's correct in mathematical terms (the stored instant-in-time is *known* to be good at UTC) it does throw away information, namely the storing-client-timezone data. If you want to retain that (rather than convert UTC to the retrieving client's timezone) you'll have to store that yourself in an extra field. I think I still prefer a more relativistic approach where times have no preferred perspective, That's meaningless. Time has, by its very definition, a perspective. It's just that for canonical times people usually agree on storing the perspective UTC. Other than that it would be akin to a byte string without encoding -- you can't tell what it means. Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On Thu, May 21, 2015 at 5:27 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Postgres does not store the time zone. When storing a timestamp with time zone, it is normalized to UTC based on the timezone of the client. When you retrieve it, it is adjusted to the time zone of the client. Sorry, I misspoke. Thank you for correcting it. It is storing it as UTC time zone. The rest of my post still applies. You will get the wrong wall-clock time for the future date because it is stored as UTC and the conversion rules will have changed giving you a different time when you convert it back to the local time zone. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE would actually save your bacon. From the postgres docs: For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future. Imagine scheduling a meeting for a certain time a few years from now. This will be stored as UTC + time zone. A year later, that government decides to change the time zone rules for their country. Your operating system will get the new timezone data in an update (as it should). However when the meeting comes around, you're going to be early/late because the wall time that you get converting back from UTC+time zone is no longer the time that you were supposed to have been at the meeting. If you had stored that future date as a timestamp WITHOUT time zone you would have still been on-time. This is only an issue for future dates, not past ones. -Brian Dunavant (time is hard, so if I'm wrong anywhere here, someone please correct me) You are wrong (or me, but I'll try). Imagine scheduling a meeting for a certain time a few years from now. This will be stored as UTC + time zone. No it won't. It will store as UTC but will not store any timezone information (apart from the fact that it knows that what is stored on disk is converted to UTC from what the client sent in for storage). You are right in the following aspect: - client sends in NOW at HERE - server knows HERE = UTC+2 - hence NOW_UTC = NOW - 2 - server stores NOW_UTC - 2 years pass - government at HERE says that from today on HERE = UTC + 4 - 2 years pass - client retrieves at HERE - server knows HERE = UTC + 4 - server also knows that HERE used to mean UTC + 2 - but server can not derive what HERE meant when NOW was stored ... - server converts stored NOW_UTC to HERE by doing NOW_UTC + 4 - client receives NOW_HERE but this became NOW - 2 + 4 IOW, the server would need to know what HERE meant when now was stored. This can only be solved by tracking insertion/update timestamps. Karsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
what you've said above is incorrect. All WITH TIME ZONE does is tell PostgreSQL to apply timezone conversions during various operations. The stored data is represented as an epoch without any concept of the source data's timezone representation. Oh, very interesting! Thank you for pointing that out. I'll have to think some more about when I'd want that behavior. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
Hi Steve, Thanks for such a thorough response! I agree that time is a lot trickier and trappier than one might expect, so it's good to learn how others grapple with it. Your original question had to do with month/year. Just to clarify, that was Daniel's original question, but you're replying to my follow-up question. The first is that web developers shouldn't become educated about the capabilities of a database but rather use the database as a dumb data-store and redo everything themselves (often this includes an utter failure to use the data-integrity capabilities of the database). That's not a debate I can hope to settle, but for what it's worth, I mostly agree with you. That's why I've written these tools to let Rails users leverage more of the capabilities inside Postgres, especially integrity constraints: https://github.com/pjungwir/db_leftovers https://github.com/pjungwir/aggs_for_arrays/ also these efforts at education: https://github.com/pjungwir/rails-and-sql-talk http://illuminatedcomputing.com/posts/2015/02/postgres_lateral_join/ http://illuminatedcomputing.com/posts/2015/03/generate_series_for_time_series/ Anyway, I agree that you have to store the time zone *somewhere*, and I suppose that's the reason Joshua remarked that you really shouldn't use WITHOUT TIME ZONE. And often a time has one perspective that is canonical or preferred, e.g. the time zone of the user who created the object. And in that case WITH TIME ZONE gives you a convenient place to store that. I think I still prefer a more relativistic approach where times have no preferred perspective, and input strings are converted to a bare instant as quickly as possible (using whatever time zone is appropriate). For instance that avoids the failure scenario Brian described. I concede that storing the time zone separately as a string makes it tricker for other database clients, at least when the string is a name only meaningful to Rails. In the future I'll keep an eye out for when WITH might be handy. And maybe I'll do some research to see how well Rails would handle those columns. Thanks again for your generosity! Yours, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On Thu, May 21, 2015 at 2:10 PM, Paul Jungwirth p...@illuminatedcomputing.com wrote: Anyway, I agree that you have to store the time zone *somewhere*, and I suppose that's the reason Joshua remarked that you really shouldn't use WITHOUT TIME ZONE. And often a time has one perspective that is canonical or preferred, e.g. the time zone of the user who created the object. And in that case WITH TIME ZONE gives you a convenient place to store that. I think I still prefer a more relativistic approach where times have no preferred perspective, and input strings are converted to a bare instant as quickly as possible (using whatever time zone is appropriate). For instance that avoids the failure scenario Brian described. I concede that storing the time zone separately as a string makes it tricker for other database clients, at least when the string is a name only meaningful to Rails. In the future I'll keep an eye out for when WITH might be handy. And maybe I'll do some research to see how well Rails would handle those columns. I'm not sure Brian is correct - but my head started to hurt when I attempted to reason it out - but what you've said above is incorrect. All WITH TIME ZONE does is tell PostgreSQL to apply timezone conversions during various operations. The stored data is represented as an epoch without any concept of the source data's timezone representation. i.e. if I store '2015-05-20T15:23:00-MST'::timestamptz into a table and later retrieve it I have no way to knowing that MST was part of the original specification. David J.
Re: [GENERAL] date with month and year
On 05/21/2015 11:56 AM, Steve Crawford wrote: On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgresql.org/docs/current/static/datatype-datetime.html Now go back and read it again and experiment a while until it makes sense. As Adrian Klaver so eloquently put it, If I have learned anything about dealing with dates and times, is that it is a set of exceptions bound together by a few rules. Every time you think you have the little rascals cornered, one gets away. This is also a very good reason to avoid reinventing the wheel. The check is in the mail:) Cheers, Steve -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 05/21/2015 11:02 AM, Daniel Torres wrote: Sorry, forgot to told you what I'm trying, I have climate data and want to obtain mean temperature and total precipitation and that sort of things per month and year. Think date_trunc is a good solution, but any other advice would be very welcome. As it turns out I am working on something similar with regards to school days. A quick and dirty query: SELECT extract ( YEAR FROM school_day ) AS YEAR, extract ( MONTH FROM school_day ) AS MONTH, count ( school_day ) FROM school_calendar GROUP BY extract ( YEAR FROM school_day ), extract ( MONTH FROM school_day ) ORDER BY extract ( YEAR FROM school_day ), extract ( MONTH FROM school_day ); Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). Results: year | month | count --+---+--- 2005 | 3 | 7 2005 | 4 |12 2005 | 5 |17 2005 | 6 |14 2005 | 7 |11 2005 | 8 |15 2005 | 9 |16 2005 |10 |15 2005 |11 |17 2005 |12 |10 2006 | 1 |15 2006 | 2 |12 2006 | 3 |18 2006 | 4 |12 2006 | 5 |18 2006 | 6 |13 2006 | 7 |11 2006 | 8 |15 2006 | 9 |15 2006 |10 |18 2006 |11 |13 2006 |12 |10 (I need to read more about time zones, I'm new at using postgresql) Thank you, Daniel 2015-05-21 12:45 GMT-05:00 Paul Jungwirth p...@illuminatedcomputing.com mailto:p...@illuminatedcomputing.com: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Are there any articles you'd recommend? I'm fond of normalizing all times to UTC and only presenting them in a time zone when I know the current perspective. I've written about that approach in a Rails context here: http://illuminatedcomputing.com/posts/2014/04/timezones/ I find that this helps me to ignore time zones in most parts of my application and cut down on my timezone-related bugs. Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
Adrian Klaver wrote: SELECT extract ( YEAR FROM school_day ) AS YEAR, Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). FWIW I think this indenting of FROM inside an extract() call is odd and ugly --- probably just an accident resulting from dealing with the regular FROM clause. It seems to me that the YEAR FROM school_day part should be considered a single argument instead of breaking it in multiple lines. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 05/21/2015 09:04 PM, Alvaro Herrera wrote: Adrian Klaver wrote: SELECT extract ( YEAR FROM school_day ) AS YEAR, Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). FWIW I think this indenting of FROM inside an extract() call is odd and ugly --- probably just an accident resulting from dealing with the regular FROM clause. It seems to me that the YEAR FROM school_day part should be considered a single argument instead of breaking it in multiple lines. Probably so, but the output is a lot cleaner then what I did in psql. The author of pgFormatter will be interested in your comments: https://github.com/darold/pgFormatter -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On May 21, 2015 11:56:52 AM Steve Crawford wrote: The article does also display a couple attitudes that I feel are especially rampant in the web-development community. The first is that web developers shouldn't become educated about the capabilities of a database but rather use the database as a dumb data-store and redo everything themselves (often this includes an utter failure to use the data-integrity capabilities of the database). Having been at the receiving end of web developer rants many times, the reason more often than not is that the database does the checking after the fact, i.e. after the user spend the time providing the data. Web developers need to know what's allowed when they throw up the page. And frameworks offer little or no help in retrieving these validation rules. So the web developer is almost forced to roll his own.
Re: [GENERAL] date with month and year
You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Are there any articles you'd recommend? I'm fond of normalizing all times to UTC and only presenting them in a time zone when I know the current perspective. I've written about that approach in a Rails context here: http://illuminatedcomputing.com/posts/2014/04/timezones/ I find that this helps me to ignore time zones in most parts of my application and cut down on my timezone-related bugs. Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column?... Others have offered good tips but if you tell us more about the problem you are attempting to solve you may get some better advice and/or warnings about pitfalls. -Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE would actually save your bacon. From the postgres docs: For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future. Imagine scheduling a meeting for a certain time a few years from now. This will be stored as UTC + time zone. A year later, that government decides to change the time zone rules for their country. Your operating system will get the new timezone data in an update (as it should). However when the meeting comes around, you're going to be early/late because the wall time that you get converting back from UTC+time zone is no longer the time that you were supposed to have been at the meeting. If you had stored that future date as a timestamp WITHOUT time zone you would have still been on-time. This is only an issue for future dates, not past ones. -Brian Dunavant (time is hard, so if I'm wrong anywhere here, someone please correct me) On Thu, May 21, 2015 at 2:56 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgresql.org/docs/current/static/datatype-datetime.html Now go back and read it again and experiment a while until it makes sense. As Adrian Klaver so eloquently put it, If I have learned anything about dealing with dates and times, is that it is a set of exceptions bound together by a few rules. Every time you think you have the little rascals cornered, one gets away. This is also a very good reason to avoid reinventing the wheel. When you need a break, watch this: https://www.youtube.com/watch?v=-5wpm-gesOY His conclusion is a good one: be very happy that someone else has done the dirty work for you. The Ruby article does make one good point which is that we are talking about what they call an instant or what I like to refer to as a point in time. The point in time is actually a better way of thinking of timestamp with time zone since the timestamp with time zone does not actually store any timezone information - it stores a point in time that can be manipulated in the time-zone of your choosing whereas timestamp without time zone is not a point in time and must be combined with other information to do proper manipulation. The article does also display a couple attitudes that I feel are especially rampant in the web-development community. The first is that web developers shouldn't become educated about the capabilities of a database but rather use the database as a dumb data-store and redo everything themselves (often this includes an utter failure to use the data-integrity capabilities of the database). The second is the assumption that they are the only users of the database and that nobody will ever access the data except through their custom-written Ruby/PHP/Perl/Python code and that no other programming language will ever be used. Woe be to the poor slob who has to deal with ad-hoc queries, analytics platforms or reporting systems that weren't so brilliantly reinvented or who wants to use range-types or other nice PostgreSQL features. Internally PostgreSQL stores timestamp without time zone in UTC but that is entirely irrelevant. What is relevant is that you can provide an instant/point in time in whatever time-zone representation you want and get it back the same way. Want to use a Unix epoch in your code. Go ahead: extract(epoch from yourtstzcol) abstime(yourepochint) Want to assume everything is UTC? No problem: set timezone to 'UTC'; Then you can reinvent wheels to your heart's content without wrecking the ability to easily use other tools. By the way, use full timezone names to avoid ambiguity. I don't know what Ruby cooked up but PostgreSQL uses industry-standard names: select * from pg_timezone_names; Your original question had to do with month/year. You will have to define this for your use-case but beware that it won't necessarily get you away from time-zone issues as the month ticks over on a zone-by-zone basis. Also note that time-intervals can be a source of interesting side-effects. Operator precedence is important. For example, what is one month? 28-days? 29? 30? 31? Every system must make a judgment call. Add a month to January 31 and you will get February 28. But add/subtract a month from February 28 and you get January 28/March 28. So you can create a query that takes a date, adds a month and subtracts a month and results in a different date. There is nothing to do here but to read the docs
[GENERAL] date with month and year
I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result is a text, not a date select extract (Year from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE)||'-'|| extract(Month from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE); Any help is welcome, thanks Daniel
Re: [GENERAL] date with month and year
On Thu, May 21, 2015 at 12:01 PM, Daniel Torres nobeea...@gmail.com wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result is a text, not a date select extract (Year from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE)||'-'|| extract(Month from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE); Any help is welcome, thanks Daniel I don't think so. Mainly because a date, at least in PostgreSQL, is by definition a month, day, and year. You could just arbitrarily set the day to 01 because every month starts with day 1, I guess. Perhaps if you said what you want to do with this type of date field? Of course, if you really wanted to, you could create your own data type and conversions. But that still wouldn't be a date, exactly. -- My sister opened a computer store in Hawaii. She sells C shells down by the seashore. If someone tell you that nothing is impossible: Ask him to dribble a football. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! John McKown
Re: [GENERAL] date with month and year
On Thursday, May 21, 2015, Daniel Torres nobeea...@gmail.com wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result is a text, not a date select extract (Year from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE)||'-'|| extract(Month from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE); You have to settle for the first of the month if you want a date type. Date_trunc(day,...) will give you that. I do end up having a lookup tha gassing sequential integers to sequential year-months to make calculations easier without having to carry around a date type for that sole purpose. For presentation I want text, not a date. User defined functions are nice here - I have a todo to publish my set to PGXN...maybe someone else already has? David J.
Re: [GENERAL] date with month and year
Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I think you want date_trunc, which will cut everything down to the first of the month, e.g 2015-01-01, 2015-02-01, etc. The results will still be dates, so you can still use date functions to manipulate them. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date with month and year
On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result is a text, not a date select extract (Year from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE)||'-'|| extract(Month from '2001-05-01 20:21:00'::TIMESTAMP WITHOUT TIME ZONE); date_part will get you what you want as will to_char. The above you could cast if you needed. You really shouldn't use WITHOUT TIME ZONE. JD -- The most kicking donkey PostgreSQL Infrastructure company in existence. The oldest, the most experienced, the consulting company to the stars. Command Prompt, Inc. http://www.commandprompt.com/ +1 -503-667-4564 - 24x7 - 365 - Proactive and Managed Professional Services! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
