MySQL: Data

Name: MySQL: Data
Author: Kati Gordon

How to manipulate date in PostgreSQL.

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

In this lesson of advanced SQL, we will discuss reformatting data. PostgreSQL provides a powerful set of tools for converting various data types, such as your datetime, integer, floating point, numeric to formatted strings. You can also convert from formatted strings to specific data types. All of these functions follow a common calling convention. The first argument is a value to be formatted, and the second argument is a template that defines the output or input format. This table shows us some of those formatting functions. We see the function identified on the left, then the return type, a description is provided, and finally, an example. So that first function using to_char is going to convert a timestamp to a string. The first argument is the timestamp, and the second argument is what you want that timestamp to look like for the output. You'll see that to_char is used for converting intervals, integers, or double precision to string, or for converting a numeric to string. You can also convert a string to a date. That's the other side of formatting, so that you can take a string and format it as a date. And this is handy when you're trying to do things in a filter, for instance. When you're filtering expressions or trying to do some comparisons, oftentimes you will need that data type to be the same. So, to_date is going to convert a string to a date. To_number is going to convert a string to numeric. Finally, to_timestamp is going to convert string to timestamp. Let's go over to our pgAdmin tool and see this in action. I've lined up some of those functions just so we can go ahead and see what that looks like in the data output window. We're gonna use select. I'll highlight it and run the query to run just that query. So let's return the time according to the format listed in the second argument. It's the same case for the interval. That's going to give us an output based on that 24 hour clock. Now let's look at this converting the numeric to a string. We can convert a string to date using the to_date function. Or convert a string to a number. And again, it's handy to have data types in the same format when you're doing comparisons, when you're trying to evaluate an expression, or when you're just trying to make the data more readable. Let's look at the templates that you have available to you. PostgreSQL documentation outlines the patterns for date and time formatting. You'll see there are quite a lot. I recommend referring to the documentation so that you can best match the function to your goal. Join us for our next lesson where we look at how to extract strings from character data.

Advanced Provider : Kati Gordon 5 Lessons · 12m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

This module discusses data in PostgreSQL. You’ll learn about reformatting data, filtering with regular data, joins, aggregation, and subqueries. PostgreSQL provides a powerful range of tools for converting various data types to formatted strings. This module dives into the functions these conversions follow. You’ll also learn about ways to report on data, including data filtering through subqueries in a variety of ways. This module will show you how to get started and how to know which method to choose.

What You'll Learn

Reformat data in PostgreSQL using functions that convert various data types to formatted strings
Filter data using regular expressions and pattern matching
Apply three approaches to pattern matching
Combine data using filtering, joins, and aggregation
Report on data using subqueries in a variety of ways
Determine which subquery method to choose for reporting on data

Key Takeaways

The module covers reformatting data, filtering with regular expressions, joins, aggregation, and subqueries.
PostgreSQL provides a range of tools for converting various data types to formatted strings.
Data can be filtered and reported on through subqueries in a variety of ways.
The module explains how to get started and how to know which method to choose.
Pattern matching can be approached in three ways.

Frequently Asked Questions

What topics does this module cover?

This module covers data in PostgreSQL, including reformatting data, filtering with regular expressions, joins, aggregation, and subqueries.

What will I learn about reformatting data?

You will learn about the functions PostgreSQL uses to convert various data types to formatted strings.

How does this module help with reporting on data?

It shows ways to report on data, including data filtering through subqueries in a variety of ways, and how to know which method to choose.

What lessons are included in this module?

The lessons are Reformatting Data Part 1, Reformatting Data Part 2, Filtering with Regular Expressions, Filtering, Joins, & Aggregation, and Subqueries.

What skills does this module focus on?

It focuses on Data Manipulation Language, Database Programming, MySQL, PL/pgSQL, PostgreSQL, and SQL.