 Hi, welcome to today's Postgres Conference webinar, time stamps, time zones, and interval arithmetic, what you need to know and what you don't need to know. We're joined by Bryn Llewellyn, Technical Product Manager at UGBITE, who will explain how Postgres and therefore UGBITE give you sufficient functionality to let you straightforwardly and correctly meet any requirements that might be set in the date time space and how they provide far more functionality than a correct implementation will need. My name is Lindsay Hooper and one of the Postgres Conference organizers and I'll be your moderator for this webinar, a little bit about your speaker. Bryn is a Technical Product Manager at UGBITE with a specialty in SQL and stored procedures in the context of distributed SQL. Bryn started off doing image analysis and pattern recognition at Oxford University programming in Fortran and then worked in Oslo, first at the Norwegian Computing Center and then in a startup. Bryn has worked in the software field for more than 40 years. He started working with SQL when he joined Oracle UK in 1990. So that's all from me. I'm going to go ahead and hand it off to Bryn. Take it away. Good, thank you very much Lindsay for that introduction. I'll assume unless anyone shouts that you can hear me clearly enough and you know the title now, the subtext or the way of paraphrasing the title is that the whole terrain, everything to do with this time stamp and date time and everything is vast and there are times sadly when you have to understand the stuff in detail but if you're lucky enough to be doing new work then you can skip tons of it and follow some straightforward guidelines to be safe and get all the functionality you need. So moving on then, who am I? I'll be as brief as I possibly can and I'll say a tiny little bit about what I expect from the audience but it's too late to change my approach if I'm wrong. So that's me and the simplest way to get the story is to just Google for Postgres Person of the Week and my name and you'll find my little essay on myself there and that's good enough plus what Lindsay just said. So this is what I'm assuming about you that you really know Postgres very well to the extent that hardly a week goes by without you typing stuff at the PSQL prompt and sometimes SQL and one would hope sometimes it's created a place procedures and functions and you certainly don't need me to tell you about why SQL is good. I'm not going to attempt that bit of proselytizing today and it doesn't upset you at all that the ideas are somewhat mature to put it mildly that the foundations of all this stuff were laid in the 1960s. Now it's not essential that you've had any exposure to what I work with UGBITB but and I'm not going to dwell on that today. The simplest thing to say is that UGBITB if you approach it from the PSQL prompt or from our slightly different variant of the same animal called YSQL or SHA then for all the sort of code that I'm showing you today you'll see no difference at all. You might see a difference with create table statements because we have special extra clauses that reflect the way we do things at the storage level you'd expect that but you can find out a lot more about UGBITB elsewhere but the simple short thing to say about it is that it brings postgres functionality that you all know and love to a scheme that has the distributed SQL benefits of the trailblazer in this space Google Spanner in other words fault tolerance scalability and so on cloud native you name it. So you may well find this whole business of dating times and all the operations you do on them mysterious and daunting that's okay because that's what today's talk is about in fact the more mysterious you find it the more perhaps your benefit from today's talk. So then without further ado I'll be skipping tons of detail because I think I can rely on the fact that it's well documented and I'll just click on this link to show it's real. Here we are this is our dock on the public internet this is the date and time major section and it's rather big and among the things it's got in it there's a top level table of content so you can see indeed it's rather big but going back to this thing down the bottom if your aim is just to do brand new work you can survive with a smallish subset of the whole lot. So that was that and then the other point is that back to the slides which are here there's downloadable code that includes various utilities that I wrote when I wrote this documentation not to keep it a secret I wrote all that documentation and to do that I had to study the stuff in depth and when I was doing it I found it very difficult frankly because this is the perfect example of where the Postgres stock is rarely if ever wrong but so terse that at times it takes a slow witted person like me forever to understand it and I have to do no end of you know empirical tests to question and confirm my understanding to date. Anyway in order to make all that lot more straightforward I've and indeed then to brain in some of the complexity I wrote various utilities here they are and without further ado I'm going to download the zip now it'll just go shooting down onto my downloads folder which is let's see if I can find it up here so there we are I've just got that thing downloaded I don't need the Safari any longer what I'm going to do is bring it into the little directory I've created for today's talk there it is and you can see three items in it and I'm going to go over to my live environment here where I'm just at the OS prompt and I'm going to check where I am for my own sake not for you and there I am I'm on the downloads folder and I'm in this particular folder for today's project and what I've got in there is my own slides which you'll get a copy of and then um directory where I've got little snippets of code and this utilities thing so if I go on to the utilities thing and if I start up our own psql I've got a little shortcut here that does it and signs me on as the super user and I've got another shortcut I'm not going to explain all this who drops if he existed the user called u1 and recreates it brand new empty and you can see there's nothing there and nothing in the way of um sorry nothing in the way of user created functions either that will change now because um if I have a quick look at what's available on where I stand sorry what have I just done hey friend yes increase the size of the fonts just a little bit of course of course of course I remind me all the time I will shrink them and grow them according to taste and I forgot to do that you don't have to read all the stuff that all you need the message of this is is that you know there's a lot there I'll do it one more time um no there's a lot of stuff there but um there's a read me and it tells you that you've just got to run this particular one guy and here we go it's all going to crank in never mind how long it takes it won't take longer than it takes me to say this sentence and when it's done you'll see and when I repeat those exploratory commands that I've got a number of views and one table which are useful in various contexts as we'll come to see and I've got a fair number of procedures you'll see this report now is illegible unless I take it down to microscopic size sorry so I don't expect you to read this don't get panicky I'm just going to take it down one step and crank it out even that was too too big but you can see there's loads of stuff okay and I'm not going to try to explain it all and I'll use a few of them and I won't even um in particular explain what how they work rather I'll just rely on them because it's all in the doc so enough of that now I'm just going to get myself ready so I don't get confused when we start by going up one and onto my code examples and there's just a number of little files there so we're ready on that you know the pump is primed and I can continue with the talk itself so here comes the main point then um so this datetime apparatus is vast and complex why is that the case well partly because they are simply inescapable facts of life astronomy is what it is you know so the earth goes round and round and round on its own axis which happens to be sloping with respect to the plane of its orbit which also plays into the picture and then um you know the earth goes around the sun and the number of rotations of the earth it takes for the earth to go around the sun isn't an integral number why should it you know don't send it off to me and it isn't and of course um human beings started caring about all this lot because of their crops and who knows what uh long time ago and all sorts of weird superstitions came in and rules of thumb and we've ended up now after all sorts of chipping away and iterative changes to the whole way of thinking with what we've got mysteriously there's a year's you know minus one and a zero plus one and no year zero who would have guessed that but that's the way it is and I won't even go into why the particular year that's missing between the two is when it is something to do with mythology that some people believe in you know it's all all that stuff plays in and how long is a month who knows 28 days 29 days 30 days 31 days those are all correct answers so how many months are there in a year someone would say 12 but it all depends how long a year on and on it goes so no wonder it's complicated um the sequel standard folks introduced some notions in succession iterations like at one point there was a date data type and nothing else and if you subtracted two dates you got an integer meaning the number of days between them then they came along with this timestamp notion which was um you know more precise in the least the world of post-quest sequel because it brought in a time of day component and then you know the same breath they brought in a time zone sensitive thing and all the whole complexity of daylight savings time and calendars and everything was unleashed in a way that it hadn't been before and subtracting two timestamps gives you an interval while subtracting two dates gives you an integer if you were doing from scratch you wouldn't have that that's what I mean by inconsistency and then early versions of post-quest implemented what I am convinced are utterly questionable at best decisions I would say wrong decisions that no written down requirements spec could ever have justified but it is what it is and nothing like that can be changed in databases because if it were changed there'd be some existing app out there that would break so given all that lot you can most certainly easily go wrong now for new work the good news you can manage with only a tamed subset of this whole shooting match and that does depend on some user defined utilities so that's my main point for today and I'll be sketching lots of it because the time is relatively limited and then um well my fallback is that it's all in the docker I wrote so then the various topics I won't tell you I'm going to do it in advance I'll just tell you as they come to it we'll talk about time zones first of all and time zones themselves are completely artificial concept really the sun is vertically ahead or as close to it can be depending on your latitude you know it's at its highest point in the sky at least um at the same moment on a given line of longitude but um if you just move a couple of meters to one side then it'll be at its highest point a little bit later or earlier depending on where you are so there's no such thing as a you know sun noon as a universal constant and pretty soon people decided it would be unworkable to have every spot on each different line of longitude having its own time scheme so they eventually one story short came to a scheme where there's time zones where all the spots in a given time zone which is loosely speaking a politically defined area of the planet they um just assert that noon is at a certain time whether or not the sun is at its peak at that moment I'm sure you all could have said that in your own different way but that's the background so people have always organized their lives by the sun and you simply can't get away from that some people like the people's republic of china do a radical one time zone policies that though that stretches over about what would be three or four time zones in the us it's the same time everywhere well that's the way they did it I'm happy for them um and there's a view called PG time zone names which lists the names and there is other properties of the time zones that at least post-class supports and there's a lot of rows there practically 600 um I'm just going to mention and I won't keep on stressing it that our U.B. is based presently on post-class 11.2 but with respect to the functionality that I'm talking about 14 doesn't read any change to it so it doesn't really matter but little details of how many rows there are in this thing change and last time I looked in um the latest I tried it was 594 for what is worth and I've tried to tame the story a little bit in ways I'll sketch later by creating an extended time zone views and deriving other things from it and this leads to two useful views that I'm going to show you now the point of canonical is that this here is based on facts that you can get off a publicly available widely respected central database and that database has all sorts of facts like for example if a given time zone is canonical well that status the post-class people didn't think was worth recording but it's very useful because for example there are two time zones one called Asia Kathmandu and the other called Asia Kathmandu but one is spelt with a T and the other spelt with a TH one of them is canonical one of them is not and if you didn't adopt some discipline and use always the canonical one you'd have a job with your usual global search and replace kind of operations so there's that sort of stuff going on and I find it very useful just to derive two views ones who do observe um daylight savings time ones who don't and they are of status canonical which means among other things that are going to be tied to a real geographical area and then the modern names for these things and there's 39 distinct offsets across these views so this is a query that using my views um can get the distinct time zones the point being that in the no DST things all you have is a UTC offset but in the ones with daylight saving there's one offset in standard time and one in daylight savings time so to get the distinct things you do just do this little dance here and um I'll just show you the technique now all these code samples that I intend to run they're in files and if I just um start up now as um number one and um I'll give myself a little bit more space there and if I run this query which is 01 sorry then sure enough there is a 39 and there's all sorts of weird animals there that happens to be Katmandu I've looked at that often enough to know where it's you know three quarters of a number um plus some whole number of hours out of step with um UTC how strange is that as another one there you know anyway um that's that point made and there it is on the slide and my recommendation is that whenever you set the time zone in the section or mention it in other syntax context and I'll come to then you use only names that come out of this and I'll show you a scheme that ensures that you do that presently right then so what is this business then of setting the time zone the main thing to understand is that you set is perhaps the wrong word specify is a better word and there are three different syntax contexts that use the specification of a UTC offset and by the way you can specify the offset either directly by name sorry indirectly by name or directly as an interval value I haven't said what an interval is but I assume that you've got a rough idea or a good idea an interval is just a data type who measures a duration of time in a rather weird way that it will come to later but um never mind that now so these are the contexts you can simply set the environment parameter with confusingly enough set time space zone name or some other spell or set time zone or one word equals and something and the syntax of what follows that can vary um or I should say different variants are allowed in those two different spellings how confusing is that um anyway that's one setting you can do it in the other is in um hey at time zone which is an operator and just to make life again harder to talk about in simple words there's a function called time zone which has identical semantics but it's more convenient to use this function usually because it's more normal in its general form you know at what is this operator it's weird you know three words in it and and then the other place is within the definition um that you make for a timestamps value might be using this function called make timestamp tz or it might be in a literal value for a timestamp tz um either way you can mention a time zone there and that's the third context so um there's a this is a perfect example of what I mean by saying that the area is littered with bombshells unless you adopt a mind fit and sorry it's a minefield unless you adopt a discipline because um sorry as we'll see in a moment this is a name to be found in pg time zone names but this which looks so similar is not and if you do this query you see only one of them come out but nevertheless if you set your time zone equal to the one who's not defined you don't get an error what the hell is going on there if you want to waste the morning you can um google and eventually perhaps you'll get to the bottom of it but you also read no end of confusion from no end of punters on stack exchange and that kind of thing going on about it long story short this happens to be a legal expression within what they call post posix syntax and that's a more explicit way of specifying facts about the time zone you want which postgres implemented in historical times because a lot of the other ideas weren't mature and because they did it then they can't take it away and we're stuck with it but it means you know you can slip up no end because if you set it to that and then you know do a query where you observe the effect of it you see oh my god it came out as plus 99 you normally used to writing america los angeles here which we know is seven hours behind and that means that the time in um you know shifts the other way and you'll see a plus there but here it's this you know oh no no no i'm not going to spell it all out otherwise i'll say the wrong not in the wrong place but basically this sorry is counter intuitive and um we can see more clearly what's going on here if we query up america los angeles which is sometimes eight and sometimes seven minus by convention and then if we query up this one which is there in the view with minus eight well here comes out as plus eight here and that's just you know like some people drive on the left and some people drive on the right these posits guys thought that um you know the offset would increase in one direction and the um other guys who inspired the basic way postgres does it took the other direction so there's two different conventions at work what's the result it can only be confusion and the moral of the story is these days this posits malaki brings no ultimate benefit because you can get full functionality in other ways so don't go near it with a barge pole and to avoid going near it with a barge pole accidentally you have to adopt some conventions and it's always better to enforce these conventions with user defined functionality functions or that kind of thing right then so um i'll just illustrate this in a second convincing the script is there but if i set the time zone to utc sort of at least i know what i'm going to see when i query up time zone sorry time stamp tz values if i set the time zone in this formulation time zone blah blah with the time in question as i would draw time stamp and if i cast it to text and do it you know using these different um spells america osangeles or make interval of minus eight hours or this here thing or god knows you know to emphasize the point foo minus eight you can guess there's a foo minus eight in the um pg time zone um names view well this is the result these two come out the same because i started at one am here um one am here then i get to nine am you know in in london let's say when it's one am in san francisco it's nine am in london and if i use these other things who look so superficially similar with a minus eight it goes the other way that's the confusion so i'm not going to run that code because the current produces this result and it's there for you to run yourself so more about the problem then um this is just the same thing in the other syntax setting where i've got a plain text thing here which is part of the specification of a time stamp tz value and um i'm just going to concatenate on to it one spelling of the time zone and the other spelling of the time zone and see what i get and here we are really getting this one comes out at nine am and this one goes the other way back to the previous year at five in the afternoon strange isn't it just all that lot can can for can confuse people no end as is evidenced by all the rubbish on stack exchange and the like but if you deliberately avoid it then you have a simple clear life right then what's the recommended practice well um there's a doc section which is exactly see if i can select this this is annoying that pdf never really works for copy and paste very well but i got it then if i go over to the docs which is here and if i um show you there exists a table of context contents which is useful to have um it's actually up there already here it is if i search in the page for that and if i go on it there we found it and you can read about that i'll um refer to very briefly now um i'm trying to give you a taster of what there is to find and show you the effect in it all so it brings to a procedure set time zone um and you can provide either a text or an interval argument and that obviously is a wrapper around the real set time zone and it just makes sure on the one hand that the text is approved uh in the sense that i went on about and if it's an interval that it's in a sensible range like you don't go shooting 99 hours into the future and then this at time zone thing is obviously a wrapper for the native time zone thing with the same overloads and that does the same thing to make sure that the whether you give it as text or as an interval it's legal in the way i just described and use these and never use the raw functionality and it ensures you get only the approved things it's might be hard you know once when you do it one morning and settle down in wrap your head around it to understand why it could go wrong if you just accept it could go wrong i'm not going to go wrong i'm going to use this method and adopt that as a rule then you'll be golden okay and here's an example it's just showing you um some weird effects and then it's showing you sorry not weird effects it's showing you using these and using sensible values and then um it's um going on to show that if you do it wrong you get some errors that i programmed in so let's just look at that that would be if i'm not mistaken number three nope sorry what did i say it was forgive me number four i skipped the end of it right and it seems to be a lot of code because i don't like seeing these errors come out raw on the screen because the error is embedded in about 17 lines of rubbish i prefer just to do the thing in a stored procedure or function it might be and um catch the exception and get the important information and printed out neatly it's one of the most annoying features of postgres there i'll say it is you can't write an anonymous block that produces sensible readable output the only way you can get several lines of readable input in some little ad hoc demonstration of functionality is to use a table function once you accept that it's not hard to do but it seems a bit of a pain to have to do it so i'm just going to run this thing um after i clear the screen sorry i always forget the i okay and that that's what i was talking about you just see sanitized and useful information so i arranged this message and it says this thing is invalid and you should instead use a thing that you find in this this view and and you can guess that this view is yet another um thing i provided in the kit there it is and it's got the name and the various abbreviations and offsets okay then um enough of that um um so we've done that we've done that point might have seemed to be a lot of talking but the upshot is simple encapsulate those um important ways of setting or specifying the time zone in the way i said and you'll be okay so now we're moving on to a different thing you know what do i mean by moment moment is a neutral term of art i've invented but i don't suppose i'm the only one who invented it to be a superclass for date plane time plane timestamp and timestamp tz timestamp with time zone there are just different ways of specifying um instant a moment in time and these are the ones you might wonder why time with time zone or time tz is not in the list well the postgres documentation says don't use it even they say steer clear of that with a barge pole it's a pain that it had to be done because the sequence standard guys thought it was a good idea i wouldn't even go into the story of why it's a rubbish idea um it's good enough for here and now leaving the postgres docs says it's it's rubbish so then um this is a simple way to view it dates well you know what a date is time you know what a time is a time is constrained to be within midnight and just before midnight the next the same day and then plane timestamp is just those two facts lumped together in one datum and then timestamp tz difficult to say this clearly but it's on disc representation is indistinguishable from the plane timestamp representation it's exactly the same facts the key thing about this is that the metadata that comes with this type rather than this type knows that it's time zone aware which means at the moment you record a value it's converted into its absolute plane timestamp value in utc and at the time you read it in other words when i say read what i mean is cast it to text um either by the typecast or by to char then that that operation is sensitive to the reigning time zone so that's the critical difference between these and long story short then um given this here even today's talk when you looked it up it said it takes place at such and such a time 1300 est actually it actually said ed t because we're still in summer time i would say that's a silly way of doing it sorry lindsay but it should have said america new york because that's one of the canonical time zones and it's up to you to know if you're in summer time or not um but nevertheless um that's what i'm getting at and um the point there obvious today almost everyone i know one of us on this call every few hours at least interacts in some way where you have to say to your mate wait a minute what where are you what's the time where you are what's the time where i are how what's the difference when can we meet when will be a comfortable time there's nothing you do that doesn't need this time zone awareness and though some people are superstitiously afraid of it if they steer clear with it and use this i'll let me far far far far worse off as i'll show you so there's a clear recommendation always prefer this one for data that you persist you can always if it's useful to you record the raining time zone offset and name by the side of it in partner columns and i'll show you in a minute how you can take advantage of that because then you've not lost any information if you used um you know plain duties plain timestamp and if you wanted the flexibility to be a bound one you'd have to do this anyway and you would have to compute your own conversions and have your own calendar somewhere rubbish so far better just to do all that lot and you can always derive plain timestamp date or time from this timestamp tz in whatever time zone you care about for display purposes or for you know on the fly purposes and that is my very strong recommendation record this this is the canonical form that you want to record derive the other things as and when you might need them if you believe you've got a good reason for not following that advice then what you must do is write up in an external documentation of your project why are you doing that and i bet you'll find as soon as you try and type those sentences and make a logical justification it'll evaporate under your feet so here's an example um this is just a little function sorry who shows you how to get the writing raining time zone offset all you got to do is um take the time at the moment it's perhaps best to take time of the ongoing transaction rather than what the world plot happens to read but that's a very subtle point and then if you use this this is the function spelling of the extract operator um to get the time zone hour and the time zone minute that will tell you the information you need all you're going to do then is convert that into an interval value and that's your answer and then um here i am creating a table obviously it's got one of these in it forgive me for using serial and not big serial or uuid or something for tests it's less typing and less to talk about and then these the important columns um when was it created as a time source yes as a timestamp tz what was the raining time zone as an interval and what was the raining time zone name as a text and what did i do right and then here we go i just set the time zone to something you know on the um left hand side of of Greenwich and um this is when i arrived in Los Angeles uh or when someone did three planes arriving at the same moment in this demo um all over the world same thing in London same thing in Kathmandu spelled with th you'll notice and um then if i query up i have to set the time zone to something so that i know what i'm seeing when i query this way and what i'm seeing is that um the actual times of day as they're recorded are given that each one happened slightly after the other identical because they've all been normalized this is the time zone um interval when it happened and this is the name where it happened and this is what happened um and now here's a demo of a more useful way to see it if i just take this thing that i recorded the time zone the created time zone sorry the created time stamp tz value and the offset that was raining at the time then i can use this guy to cast it to a plane time stamp and with it as that status then i can cast it either to a date or time and use these format masks to display it in a more conventional fashion and this is my result so thinking of local time i arrived on tuesday this date at that time in the evening and at exactly the same moment a colleague in a different plane in a different part of the world arrived in london as it happens on the next day in the small hours of the morning and the third colleague also when his plane touched down at the same identical moment arrived in Kathmandu even later in that next day and that's a more useful way of seeing it so this is my assertion for you that um you get exactly what functionality you would ultimately want by doing things in this sensible fashion and you don't need to understand anymore so just to prove a point now then this was number five so um here it is this is everything i showed you don't have to show you one more time and um if i just run it i assume you're going to believe me for most of the time now there's no real value in showing this time and again unless i want to enlarge a bit of this print and run it for itself but what i could do but i'm not going to waste time on it now is do this identical thing using vanilla postgres and you'd see that the results are indistinguishable right then back to the plot am i doing not too bad um now we're going to look more closely at this time's own sensitivity of the conversion of a timestamp's value to a text value and i have to say that many people i speak to even among my colleagues can't formulate their thoughts in this space in any clear way so for example they'll have a timestamped tz column in a table they'll select it and they'll see stuff on the screen and without really thinking they think they're seeing that value of the timestamp tz value as it's recorded they don't understand that the psql and all other things like it that have to show the end user text inevitably have to do a conversion from timestamp tz to a text value either they do it as psql does it as an implicit time sorry implicit typecast that unless you think clearly you forget this happening or they do it with two char but both of those operations the implicit time cast actually explicit time cast two time cast two and the two char they inevitably are sensitive to the reigning time zone and if you don't keep that first and foremost in your mind and especially when you do experiments to show yourself that you understand what you think you understand i'll tell you you will just confuse yourself and anyone you try and discuss you so here's a contrived app that does things right but it makes the point and it's you know a ludicrously slimmed down version of an app whose purpose is to create meetings one person creates a meeting wherever they happen to be and other people can view it because they're interested in it too and the assumption is they're all using some tool like a conventional calendar app on any old laptop that itself has a preference setting somewhere that records the time zone you want to think you're in when you're doing this of course if if i'm off in London and i'm trying to arrange some meeting somewhere i might just set my time zone to back home so i don't get too confused and then in my preferences and then set it back to where i really am at the moment that's beside the point there's always a time zone known in the environment of these apps where they do stuff so then here i've got myself a table meetings and i'm just going to have a primary key in it of course and the time of the event this is as slimmed down as it could be i'm not even going to bother to say what the event's about and then i'm going to make myself a little unprepared statement who inserts two rows that's what i mean by saying it's a bit of a contrived app but this is the if you like this is the app and there's another query sorry another prepared statement who queers things up and notice that it's spelt in a way that doesn't mention the time zone at all it's expecting that it'll be sensitive to the time zone in the environment obviously i could have done stuff in here to hard code the time zone that i wanted to see things out in some way but that would be not useful so i'm just getting out the meeting ID and when it is i hope that makes sense and then here's the scenario ricky and ricky d jones obviously she lives in la and she adds two meetings and views what she's got show this is happening though she didn't notice it in her environment of her calendar references and there they are the two meetings and the dates are cunningly chosen to be before and after the spring forward date here in la or there in la i should say but here in california and then when it's done when the meetings are created we in the same time zone of course query to see what we've got and what we get is we see yeah they're both as i wanted at eight o'clock in the morning i know i have to do that because some guy is going to be in europe and um one of them is the ninth of march and the other is then oh but oh look here's this little subliminal clue they're both in la um america but um one of them is pst you know standard time the other is daylight savings time obviously because we're before and after the spring forward date that's why the minus eight and the minus seven so if she really cared to think about it ricky would work out that some guy in amsterdam might you know experience something funny here uh because here we are vincent vincent van goch he obviously lives in amsterdam um he views his meetings but obviously this is happening unbeknown to him explicitly it's just in his preferences and when he queries up he sees while they're on the dates i expect but could got one of them was at 17 and the others at 16 what what a pain has has um you know ricky whimsically changed the time in the meeting for this other one well he hardly needs to know all he needs to know is that um they're at those times on those dates from his point of view um but if he looks more closely he'll see um well they're in the same time zone and if he got on the phone and asked ricky what's going on she would eventually fess out that are there's been a daylight savings time and then they'd both argue about it and they'd both get on the wikipedia and they'd find that stupidly the country that still uses inches and feet and so on changes its daylight saving on one date and the continent that uses the metric system changes on a different date there are many examples like this don't get me started on the how you spell a date whether it's month day year or the other way around and anyway the explanation is clear but no one wants to have to think all that not through they just want it to happen implicitly and i hope you can see that this code here guarantees it simply happens implicitly that's the point if you use the time zone tz data type religiously and properly then you'll get what you want without having to write any special code so that by the way you just a note this um thing called epoch is um the number of seconds since a certain date happens to be the unix magic type in 1970 you know the first the very first instant in the year of 1970 in utc and because it's thought of that way then when you get the epoch the number of seconds since that thing for a timestamp tz value then it's not affected by the session time zone and here's a little demonstration of it doesn't matter whether i um query up this particular um timestamp value in um a reigning time zone of what's good for la or an arraigning time zone of what's good for catmandu you know minus seven or plus eight and three quarters sorry plus five and three quarters the result is the same in both cases okay um and sometimes that's useful because this is the closest thing you have that lets you actually inspect the real recorded timestamp tz value without any complexity of casting to text and you know taking acknowledgement of the reigning time zone confusing the picture okay then now we're on to interval arithmetic and i should say that this is a huge topic and i'm planning to submit a talk on entirely and only this for the in-person postgres bash in um san jose in the new year but i'm going to give you a little you know taster of the main highlights here and now and essentially i'm going to show you examples where weird things happen and caution you that you don't want weird things to happen and therefore you should do things right so though i haven't written the words on the screen the thing that informs this whole bit of code here is something that you can read largest life in the postgres doc though it's exceedingly tersely and i would say confusingly expressed it says that the internal representation of an interval value is three fields months days and seconds it doesn't really say why or what the consequences are except hinting in some mysterious way that this has got something to do with you know the magic of calendar time and human convention and everything and it's supposed to do what you want but you might get confusing results it says how useful is that anyway this is just a scheme who shows you what the three fields really are now it relies on this function here which is one of the things that comes in the environment if and only if you download my kit there it is and it's got two overloads the one that's interesting here i won't bother to say what the other ones for is if you give it an interval value it will give you a user defined type who's got these three fields in it and all i've got here is a little wrapper around that who makes a pretty um pretty display of it what number is this one this is number eight let's just look at number eight and if i run in this function just by itself here first i don't know if you know about these x on and t on things they um the x on thing twizzles the columns and the rows kind of pivot so you can read what would be a very wide row much easier and this other one gets really extraneous column headings now if i just do this on a few example values let's shift this over and make this a bit bigger now clear away all my back and just spray it in then what we see is when i use this make interval for one month i get a pure interval value in the sense that it's got a non zero field in only one of its fields it's a pure month's value 30 days gives me a pure days value and that many hours gives me a pure seconds value but this thing here which is perfectly legal some non integral number of months gives me this animal a hybrid and as we'll see in a minute it's basically utterly meaningless it's meaningless in two ways the rules for computing how on earth you get this from this are not documented by postgres though i did an empirical investigation and produced a you know a pl pg sql implementation that in all tests mimics it and if you read it that's a kind of an external spec of what it does but you'll see that there's no rhyme or reason for it some of it is actually utterly whimsical and stupid it is what it is you get what you get so you can't predict what you get because it's so complex that you'd have to wrap your head in a towel and study it for an hour before you knew what to expect from this and then anyway it's a mixed or hybrid interval value which as we'll see presently is just a recipe for disaster so that i hope i'm not preaching too much i hope i'm making the point sufficiently forcefully you'll never read on this lot this is number eight continued so here it is what i've got here is um just a table function if this was oracle i wouldn't have used the table function i would have used a ordinary anonymous block with um dbms output but you can't do that in postgres so that's why it's a table function but if you just accept this locution i set it up returning a table of rows that are just one field text and i create whatever i want in that text and i return it so you know you can almost ignore these things on the right which is why i try to stick them out the way on the right with some you know ordered white space the main point then is that i've got a value here which is timestamp tz and i form it in a certain way i form it in a way which is sensitive to the time zone that's passed in as you see here okay this bit is fixed but this bit is sensitive to the time zone i get this value um in other words it's transformed into utc or relative to utc for storage in the lights of this and then i've got intervals i1 i2 and i3 that are the ones where i just showed you the fewer months the fewer days and the fewer seconds okay and then all i'm doing is doing the type sorry the text typecast of those interval values i'm showing the internal representation of the same values then i'm asking if they're equal surprise surprise they will be but clearly you'll see they're not in any proper sense that's just because the native equals operator for its overload with two interval arguments is arguably stupid though some people would say it's sensible clearly if you try telling the post gris um hackers that it's stupid they'll tell you you're wrong and it's sensible um but it's easy to create a user defined operator and you can find all the um way to do it in the doc and what it does is it's strict whereas the other one is casual and we'll see that this one gives false and this one gives true and then i'm looking at the effect of adding these intervals and then seeing do i get the same answer in each case and well we'll see what we see so in one case i'm doing it in a time zone LA which is sensitive to daylight savings time and by the time we've gone from here conveniently contrived with an interval this big we've crossed that transition point and then i'm doing it in catman do where they don't honor daylight savings time so let's just blast that one through then that's um what number is that number eight i hope you're still with me here under my get out of jail card is that you can look at all this in your own time more slowly and you can check all the bits that i skipped over in record time in the docs so this is um the text typecasts of my interval values this is their internal representation we saw that already each is a pure interval value of the different flavors months days and seconds this native equality test shows stupidly my by my book that they're the same they're in some sense their equivalent because by some rule of thumb you know um this many seconds is indeed one day and this many days by assertion is indeed one month of course everyone knows that some months are 30 some are 31 and guess what some of 28 and 29 too depending on all the things you know but um nevertheless they're considered all to be equal but this one just does a you can guess field by field comparison for this one of mine to come out true it has to be field wise equal in the comparison and no wonder it comes out false then and then here's the time stamp i started with and then here are the results of adding those different interval values which are normally according to one way of looking at it the same as each other and it may be hard to read all that lot but long story short all of them come out different right what's going on here briefly um when you add months it respects one human convention namely if i'm on the 16th of march and i add a month i end up on the 16th of april fired another month and end up on the 16th of may in other words who cares how many days they happen to be in the months if i'm on the you know 15th or 14th of january and i add a month sorry 14th of february valentine's day and i add a month well i'll be in the 14th of march never mind if it's a leap year or not and never mind how many days there are in february and either of those two cases neither being 30 because that's the way human beings think have come to think about what months mean you know when someone says i'll have this ready for you in three months no one asks where you talk about february in this lot and is it a leap year um not at all now when you add days it's a different convention it's just you know if you're on the 14th of february you add five days you're on the 19th if you add enough days to take you into the next month however many days that month might be that is reflected but you end up at the same time of day and just think about it you know you arranged an international zoom the major yours on the saturday in la just before daylight savings time twizzle of course he's in amsterdam and they don't do that selenus they wait another couple weeks and um you say let's just push it out one day i can't make it today what you think you're saying is well it was arranged for um five o'clock sorry not five o'clock noon my time in other words nine hours ahead of that for this guy um nine in the evening and um you think when you say push it ahead of day it'll still be the same thinking uh maybe it will maybe it won't you know if it's just a local conversation you and your mate down the street then pushing it out today even when it crosses the time zone change sorry the daylight savings time change conveys something humanly but if you're thinking now of it in those international terms or conversely if you're thinking about it in terms of a airplane flight that you know it's duration for and it's you know you're either going to take a saturday flight or a sunday flight it to europe at this magic moment the 15th of march well you know that your arrival time will be different but each end according to whether you leave at the saturday or the sunday because of this business going on and that's what this business here reflects that's why starting on the 10th you end up on the 10th by months arithmetic that's why starting on the 10th you end up on the ninth by days arithmetic and that's why starting at noon you end up at one in the afternoon when you're using seconds arithmetic each of those is a rule who's defined for a certain use case you'd better understand what use case you have and choose the right rule and if you ever end up with a hybrid value you can't choose the right rule there is no right rule because the whole thing is just a massive chaos so that's the whole point that i was trying to make with this i hope that's clear i'm going to have to rush a bit to reach the end but i might just go over a moment to you are we doing or i'm over a moment to you already right then so um this is the example and i showed it to you already down here i'll just show the second half and that is in a time zone in sorry daylight savings change agnostic world you still get this effect going on in the 10th or the 9th but these two are now the same as each other as is shown by that because there was no daylight savings change in the picture the same would happen in in la if you happen to start in july and go forward into august because there's no daylight savings time change then right so this is the main point there these rules are sensibly defined for different distinctly different use cases you need to know what your use case is and if you have a hybrid value well you've got no rule going on because it's all just mixed up into chaos so then here's another example um it's much the same kind of thing but a different twist on it um again maybe it'll be simple if i just look at it over here in the um view where i can scroll my text and shrink and enlarge it um we're almost at the end number nine all i'm doing is defining a certain time stamp and a different time stamp and then i'm subtracting them to get an interval and then i'm taking that difference and i'm adding it to the first one to get a new value and then i'm comparing if if it comes out as i could expect and here it is in more plain english there's t1 there's t2 there's the difference and there's the different you know the first one plus the difference and if you and algebraically simplify that you'll see that's t2 and then i'm saying is this thing who simplifies down to t2 equal to t2 well guess what um if we do number nine now okay it's false they come out different well that's just life if you like of course i cunningly contrived it here that i had a mixed value i didn't exactly contrive it i simply subtracted two times stamps and it's a fact that if you use the raw functionality and subtract two time stamps to define of impart you get a hybrid interval value which never has a non-zero months part but typically has non-zero days and seconds parts ergo you're going to get um in a mess if you try and use it to add on to anything so um there's one final example and then the point is made really um it's just uh another variation on the same sort of thing and i'm not going to show it now i'm just going to get on to the um recommended practice and basically what i'm saying is um i've thought all this through the only way to manage is to create some custom domain types for specializing the things and in other words they ensure that you get a pure months pure days or pure seconds interval value no matter what you do because the function that goes with the domain definition ensures that so either you get errors if you do something wrong or you'll know you're right and um i'm not going to go into all this now but the moral is it might seem dauntingly complicated but it's easy enough to do it once you accept that it's the best practice so here's the summary you've seen many ways you can reduce nonsense results here's how to avoid it use only time stamp tz to persist daytime values and if you want to um record this extra information by the side and then you can always derive what you need when you need it be aware of it tomorrow arithmetic and the way to do that is by adopting the practices um here actually that's a more general recommendation and here for the interval stuff and to write brand new application code if you're simply to have happy to accept this without obsessing about on all the reasoning that supports it you don't have to study too much in fact just these sections out to the vast entire daytime section and otherwise if you have to maintain extant daytime code especially for you if it's poorly commentated has no external design doc and its authors have just disappeared off the face of the earth well you just have no choice but to study the whole shooting match make sure you understand it and then try to weed out in the extant code things that have clearly been implemented according to your best guess of a sensible functional spec that doesn't exist wrongly so then enjoy it and finally i'm not going to spell this out this just reminds you what you go by is in the world it's the best of postgres and google spanner combined and there's all sorts of places you can read about it and get on with it so then i'm done now i'm sorry i went on just a bit i'll just blame that on the complexity of the topic my enthusiasm and some wasted moments at the top so then hey that was great thank you so much brand um you've now come to understand time stamps and all that rubbish oh you know i'm not technical but i understand that now well you understand it's hard stuff i assume oh goodness so then with that um i think let's call it um brand thank you once again thank you so much um your presentations are always so great with the postgres conference series and to all of our attendees thanks for spending a little bit of your day with us and i hope to see you at future postgres conference webinars