When your data has a temporal aspect, dealing with time, time zones, and time-based relationships gets very complicated quickly. In this course, PostgreSQL: Time and Temporal Data, you'll learn how to handle the time dimension of your data in a structured way. First, you'll learn about the date and time related types, and functions built into PostgreSQL. Next, you'll explore how to work with time zones. Finally, you'll discover how to design temporal tables, and how to query and maintain data in them. When you’re finished with this course, you'll have the skills and understanding of temporal data and time representations needed to manage temporal data effectively. Software required: PostgreSQL.
Course Overview Hi everyone. My name is Alex Korban. Welcome to my course, PostgreSQL: Time and Temporal Data. I'm a freelance developer and author and I have been working with Postgres for the last decade focusing primarily on time series and geospatial data. In almost every database you will find time values and temporal data. Time is a fascinating subject, but dealing with time calculations, time zones, and temporal relationships gets very complicated quickly. In this course you will learn a structured approach for handling the time dimension of your data. First, you will learn the rich repertoire of date and time related types and functions provided by PostgreSQL. Next, you will learn how to work with time zones and daylight savings time. Then you will find out how to structure your tables according to the kind of time related questions you need to answer. Spoiler, there is more than one type of time. After that you will learn how to build SQL statements to effectively create and modify temporal data. Finally, you will find out what to consider when interfacing with client applications. When you're finished you will have a good understanding of time representations and temporal data and a solid foundation for managing temporal data effectively. Please be aware that this course requires familiarity with PostgreSQL. I hope you will join me on this journey to learn about handling temporal data in Postgres with the PostgreSQL Time and Temporal Data course.
Working with Ranges, Current Time, and Sequences In this module we are going to cover more basics. I will discuss Postgres support for range types, in particular, its range types for dates, timestamps, and times. I will talk about the functions and operators available to work with ranges. You will also learn how to obtain the current time. There is some interesting nuance in that. Finally, Postgres provides a very useful set returning function called generate_series, which allows you to generate sequences of dates or timestamps. We will look at how it can be useful when working with temporal data.
Dealing with Time Zones In this module we are going to discuss how to deal with time zones. As long as your database is only used at one location you don't need to worry about time zones, but when you have temporal data tied to different locations around the world you may get different local times as input, and you may need to provide local times as output. To deal with it you can rely on PostgreSQL support for time zones. In this module we are going to complement our compendium of time related types with types which are capable of storing time zone information. We will also look at the way Postgres stores values of these types and the tools it provides for converting timestamps between different time zones, but first we need to talk about time zones and how they work because they can definitely be tricky.
Querying and Maintaining Temporal Data In the previous module we have considered the different types of time and looked at setting up temporal tables. In this module I'm going to focus on dealing with data. I'll demonstrate a few general purpose techniques for working with time values. Then I will continue exploring temporal tables and talk about maintaining and querying the data in the temporal context. I'll finish up with some tips for maintaining high performance as the as the volume of data increases. I will continue using the example scenario of collecting data from my highway construction site that I introduced in the previous module.
Interacting with Client Applications In this module we are going to look at dealing with time from a broader perspective and examine some aspects of the interaction between the database and the applications that exchange time values with it. I will talk about sending and receiving dates and times. I will highlight some things to pay attention to in dealing with time zones. I will discuss the issues with using timestamps for concurrent modifications and synchronizing data with clients. I will also talk about dealing with duplicates. Finally, I will give a couple of tips for testing where it involves a database.
Managing Time Related Configuration In this module I am going to show you the time related configuration available in Postgres. I will talk about the database server and client session options related to time, such as input and output formatting. I will talk about time zone abbreviations and how they can be customized. Finally, I will discuss the IANA time zone database and how it gets updated.