PostgreSQL: Time and Temporal Data

Pluralsight

Course Summary

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 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.

Course 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.

+
Course Syllabus

Course Overview
- 1m 38s

—Course Overview 1m 38s

Getting Started with Times, Dates, and Intervals
- 46m 35s

—Overview 6m 59s
—Date Type 2m 48s
—Time Type 2m 44s
—Timestamp Type 1m 26s
—Interval Type 6m 42s
—Date Operators and Functions 8m 43s
—Time Operators and Functions 3m 10s
—Timestamp Operators and Functions 5m 59s
—Date and Timestamp Gotchas 2m 2s
—Interval Operators and Functions 5m 24s
—Summary 0m 34s

Working with Ranges, Current Time, and Sequences
- 25m 54s

—Overview 0m 45s
—Range Types 3m 41s
—Date Range, Range Functions, and Operators 8m 19s
—Timestamp and Time Ranges 1m 15s
—Overlaps Operator 1m 18s
—Current Time 5m 25s
—Sequence Generation 4m 12s
—Summary 0m 55s

Dealing with Time Zones
- 26m 44s

—Overview 0m 56s
—Time Zones 8m 32s
—Time Zone Aware Types 7m 54s
—Time Zone Conversions 2m 34s
—Gotchas 3m 24s
—Other Scenarios 2m 34s
—Summary 0m 47s

Keeping Track of Changing Entities
- 47m 33s

—Overview 3m 18s
—Problems Solved with Temporal Data 6m 31s
—Avoiding Temporal Data 6m 38s
—Types of Time and Time-varying Tables 5m 21s
—Constraints for Different Types of Time 4m 48s
—Transaction Time Tables 5m 18s
—Foreign Key Constraints 6m 49s
—Valid Time Tables and Bitemporal Tables 3m 47s
—SQL:2011 Temporal Features Overview 3m 34s
—Summary 1m 24s

Querying and Maintaining Temporal Data
- 33m 34s

—Overview 0m 42s
—Techniques for Working with Time Values 7m 48s
—Querying Temporal Data 2m 34s
—Sequenced and Non-sequenced Queries 10m 24s
—Modifying Temporal Data 5m 49s
—Performance Considerations 5m 22s
—Summary 0m 53s

Interacting with Client Applications
- 21m 16s

—Overview 0m 40s
—Sending and Receiving Dates and Times 6m 34s
—Concurrent Modifications 7m 57s
—Dealing with Duplicates 3m 23s
—Using Postgres to Help with Testing 2m 1s
—Summary 0m 38s

Managing Time Related Configuration
- 13m 44s

—Overview 0m 27s
—Configuration Options 7m 51s
—Time Zone Abbreviations 2m 17s
—IANA Time Zone Database 1m 58s
—Summary 1m 9s