PostgreSQL: Time and Temporal Data

In almost every database you will find time values and temporal data. This course will teach you how to deal with time, time zones, and more complex temporal data in PostgreSQL effectively.
Course info
Level
Intermediate
Updated
Aug 16, 2017
Duration
3h 37m
Table of contents
Course Overview
Getting Started with Times, Dates, and Intervals
Working with Ranges, Current Time, and Sequences
Dealing with Time Zones
Keeping Track of Changing Entities
Querying and Maintaining Temporal Data
Interacting with Client Applications
Managing Time Related Configuration
Description
Course info
Level
Intermediate
Updated
Aug 16, 2017
Duration
3h 37m
Description

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.

About the author
About the author

Alex Korban is an author and consultant with an interest in functional programming, databases and geospatial applications. He co-founded a company to visualize geospatial data and wrote several books.

More from the author
Building Real World Applications with Elm
Intermediate
2h 18m
Feb 2, 2018
More courses by Alex Korban
Section Introduction Transcripts
Section Introduction Transcripts

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.