Close

プル リクエストがマージされたときの Jira Automation ルール

Last modified: December 09, 2019

DATE and TIME values in PostgreSQL have a whole special set of functions and operators for their proper use. So many queries deal with DATE and TIME information that it’s important to get to know the date tools. Below we’ll cover and practice the main functions you’ll likely need. If you want to get detailed you can checkout the full list of PostgreSQL functions here.


アジャイルを超える


There are 4 main ways to store date values in a PostgreSQL database:

DATA TYPE

DESCRIPTION

EXAMPLE

OUTPUT

TIMESTAMP

DESCRIPTION

date and time

EXAMPLE

TIMESTAMP '2023-04-10 10:39:37'

OUTPUT

2023-04-10T10:39:37

DATE

DESCRIPTION

date (no time)

EXAMPLE

DATE '2023-04-10 10:39:37'

OUTPUT

2023-04-10

TIME

DESCRIPTION

time (no day)

EXAMPLE

TIME '2023-04-10 10:39:37'

OUTPUT

10:39:37

INTERVAL

DESCRIPTION

interval between two date/times

EXAMPLE

INTERVAL '1 day 2 hours 10 seconds'

OUTPUT

1 day, 2:00:10

We’ll go over more about each of these.

アジャイルを超える


Dates in a database aren’t stored as strings, but we input and fetch data from it as if it were a string with the following format for the information:

YYYY-MM-DD HH:MM:SS

where the letters stand for Year, Month, Day, Hour, Minutes and Seconds. Let’s say for example that we want to record that we got a new user on April 10, 2023 at exactly 10:39. To represent that exact date and time we would use the format:

2023-04-10 10:39:00

TODO: this format is also supported: January 8 04:05:06 1999 PST

To get some familiarity try creating and SELECTing a few TIMESTAMPS below. I was born on May 1st, 1983 at exactly 4:00am. Can you fetch that timestamp?

SELECT TIMESTAMP '2023-04-10 10:39:37';

We’re just going to jump in here. We need to use a different table as none of the previous ones we’ve been using have had date fields in them. Another table available to us in chinook is employees. Let’s get familiar with what columns are in this table by looking at the first few rows. Note that there are several columns so you may have to scroll right to see all of the data:

SELECT * FROM employees LIMIT 3;

Each employee has two TIMESTAMP columns, one for their birth_date and one for their hire_date. You can use all of the ORDERing, GROUPing and other functions we learned for other columns on DATE columns as well. Try getting a list of the 4 youngest employees in the company.

アジャイルを超える


Often you don’t want to show the full raw TIMESTAMP, but rather a nicely formatted, potentially truncated version. For example, let’s say we want to get a list of the employees names and the year that they were hired. To do so we’ll need to parse the hired_date to just pull out the year. We can do so with the TO_CHAR function which works as follows

TO_CHAR([date type], [pattern])

where [date type] is a column or value of any of the above listed date/time data types, and [pattern] is a string indicating how to format the output date. The main symbols you’ll want to use to create your format patterns are here

PATTERN

DESCRIPTION

EXAMPLE

OUTPUT

HH

DESCRIPTION

Hour (01-12)

EXAMPLE

TO_CHAR(TIME '4:15 pm', 'HH')

OUTPUT

04

HH24

DESCRIPTION

Hour (01-24)

EXAMPLE

TO_CHAR(TIME '4:15 pm', 'HH24')

OUTPUT

16

MI

DESCRIPTION

Minute

EXAMPLE

TO_CHAR(TIME '4:15 pm', 'MI')

OUTPUT

15

SS

DESCRIPTION

Seconds

EXAMPLE

TO_CHAR(TIME '4:15:23 pm', 'SS')

OUTPUT

23

am

DESCRIPTION

displays whether time is am or pm

EXAMPLE

TO_CHAR(TIME '4:15 pm', 'am')

OUTPUT

am

YY

DESCRIPTION

last 2 digits of the Year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'YY')

OUTPUT

23

YYYY

DESCRIPTION

4 digits of the Year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'YY')

OUTPUT

2023

MM

DESCRIPTION

Month # of the year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'MM')

OUTPUT

04

Month

DESCRIPTION

written Month of the year capitalized

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'Month')

OUTPUT

April

Mon

DESCRIPTION

abbreviated of Month of year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'Mon')

OUTPUT

Apr

DD

DESCRIPTION

Day # of the month

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'DD')

OUTPUT

10

Day

DESCRIPTION

written Day of the week

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'Day')

OUTPUT

Monday

Dy

DESCRIPTION

abbreviated Day of the week

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'Dy')

OUTPUT

Mon

WW

DESCRIPTION

Week # of the year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'WW')

OUTPUT

15

Q

DESCRIPTION

Quarter of the year

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'Q')

OUTPUT

2

TZ

DESCRIPTION

TimeZone

EXAMPLE

TO_CHAR(DATE '2023-04-10', 'TZ')

OUTPUT

UTC

The above patterns can be string together to get the format you eventually want. Some common outputs are:

SELECT TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY');

and

SELECT TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS');

and

SELECT TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'MM/DD/YY');

You don’t have to memorize these (it’s hard to!). It’s just good to get familiar with how it works and then reference back to it when you need it in the future.

アジャイルを超える


There are a couple of extra tools you can use on patterns that output numbers.

FORMATTER

DESCRIPTION

EXAMPLE

OUTPUT

FM

DESCRIPTION

Fill Mode will remove any 0’sat the front of a 2 digit number.

EXAMPLE

TO_CHAR(DATE '2023-04-05', 'FMDD')

OUTPUT

5

th

DESCRIPTION

adds the ordinal suffixeslike st, nd or th to the end of a number

EXAMPLE

TO_CHAR(DATE '2023-04-05', 'FMDD')

OUTPUT

05th

And of course you can combine the two to get

SELECT TO_CHAR(DATE '2023-04-03', 'Month FMDDth');

アジャイルを超える


For string outputs, most of the patterns above support different casing output based on the case you use for the pattern. Some examples using different casings of “Day”:

SELECT
 TO_CHAR(DATE '2023-04-10', 'DAY') AS "DAY",
 TO_CHAR(DATE '2023-04-10', 'Day') AS  "Day",
 TO_CHAR(DATE '2023-04-10', 'day') AS "day";

And you can see the following common date format in UPPERCASE, Capitalized and lowercase formats:

SELECT
 TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'FMHH:MMAM DAY, MONTH DDTH YYYY') AS "UPPERCASED",
 TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'FMHH:MMam Day, Month DDth YYYY') AS "Capitalized",
 TO_CHAR(TIMESTAMP '2023-04-10 10:39:37', 'FMHH:MMam day, month FMDDth YYYY') AS "lowercased";

Note that the case for numeric values doesn’t change. Still use DD for the day # of the month and YYYY for year.

We’re going to move on in the tutorial but if you’d like more details checkout the full list of PostgreSQL date formatting functions.

アジャイルを超える


PostgreSQL supports a number of special values, or functions to help bet the current DATE, TIMESTAMP or TIME. The most used ones are

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP

and they are used by just putting them in the query

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

アジャイルを超える


In analytic queries, it’s very common to group things by dates. For example you may want to see new users by year, month, week or day. To do so, you’ll want to use the TO_CHAR function to convert the dates into a truncated string before you GROUP BY it. You don’t want to simply GROUP BY the raw date as those are accurate down to the millisecond so grouping by the unaltered date would be like making GROUPs for each millisecond.

The following examples are using the hire_date field from the employees table and show a lot of common formats you can use for these groups. These are what we use at Atlassian Analytics for our date group formatting standards.

GROUP PERIOD

EXAMPLE SQL

EXAMPLE OUTPUT

SECOND

EXAMPLE SQL

TO_CHAR(HIRE_DATE, 'YYYY-MM-DD"T"HH24":"MI:SS')

EXAMPLE OUTPUT

2018-03-04T00:00:00

Minute

EXAMPLE SQL

TO_CHAR(hire_date, 'YYYY-MM-DD"T"HH24":"MI')

EXAMPLE OUTPUT

2018-08-14T00:00

Hour

EXAMPLE SQL

TO_CHAR(hire_date, 'YYYY-MM-DD"T"HH24')

EXAMPLE OUTPUT

2018-01-02T00

Day

EXAMPLE SQL

TO_CHAR(hire_date, 'YYYY-MM-DD')

EXAMPLE OUTPUT

2003-10-17

Week

EXAMPLE SQL

TO_CHAR(hire_date, 'IYYY"-W"IW')

EXAMPLE OUTPUT

2002-W33

Month

EXAMPLE SQL

TO_CHAR(hire_date, 'YYYY-MM')

EXAMPLE OUTPUT

2002-05

Quarter

EXAMPLE SQL

TO_CHAR(hire_date, 'YYYY"-Q"Q')

EXAMPLE OUTPUT

2003-Q2

Year

EXAMPLE SQL

TO_CHAR(hire_date, '"Y"YYYY')

EXAMPLE OUTPUT

Y2012

Hour of Day

EXAMPLE SQL

TO_CHAR(hire_date, 'HH24')

EXAMPLE OUTPUT

14

Day of Week

EXAMPLE SQL

TO_CHAR(hire_date, 'FMDay')

EXAMPLE OUTPUT

Thursday

Day of Month

EXAMPLE SQL

TO_CHAR(hire_date, 'DD')

EXAMPLE OUTPUT

17

Day of Year

EXAMPLE SQL

TO_CHAR(hire_date, 'DDD')

EXAMPLE OUTPUT

125

Month of Year

EXAMPLE SQL

TO_CHAR(hire_date, 'FMMonth')

EXAMPLE OUTPUT

October

Feel free to try out any of the above formats on the query below:

SELECT TO_CHAR(hire_date, '"Y"YYYY') AS "Year Hired",
    COUNT(*) FROM employees
    GROUP BY "Year Hired";

There are only 8 employees in our database so we’re not dealing with too many groups there. You can get a little more granular with the invoices table and it’s invoice_date column with 250 rows.

SELECT TO_CHAR(invoice_date, '"Y"YYYY') AS "Year Invoiced",
    COUNT(*) FROM invoices
    GROUP BY "Year Invoiced";

The above query returns the number of invoices created per year. Can you modify it to get a SUM of the total amount invoiced by month?

Written by: Dave Fowler
Reviewed by: Matt David


次のトピック