search

anthonydb / practical-sql

Code and Data for the First Edition of "Practical SQL" by Anthony DeBarros, published by No Starch Press (2018).
Other
662 stars 409 forks source link

Chapter 13: Datestyle Question #36

Closed robml closed 2 years ago

robml commented 2 years ago

Having trouble with data, code, or exercises in Practical SQL? I'm glad to help. Please answer these questions, and I'll typically reply within one to two business days.

Please include the chapter number in your issue title. For example: "Chapter 13: Regular Expression Question"

General

Please answer all of these as they're essential for troubleshooting.

What's your operating system (e.g. Windows 10, macOS Catalina, Ubuntu, etc.)? Windows10

Did you install PostgreSQL and pgAdmin according to the steps on page xxviii of the book's Introduction? If not, please describe your installation. Installed as per instructions.

Which versions of PostgreSQL and pgAdmin 4 are you using? If you aren't sure, for PostgreSQL run the SQL command SELECT version(); and for pgAdmin 4, navigate to "About pgAdmin 4" under the "Help" menu.

PostgreSQL14 and pdAdmin 6.1

Did you download the book's code examples and data from GitHub using the directions on page xxvii of the Introduction? If not, please describe how you loaded the material on your computer.

Downloaded as per instruction.

Issue or Question

Chapter, page and code listing number: Chapter 13, Listing 13-9

Describe the issue or your question: So this is not the first time this has come up, and it has to do with loading dates formatted differently from UTC into my database. In the Regexp Chapter we match American style dates in the "MM/DD/YY" format. However when I attempt to update my table, I get the error below. After some research it seems I have to change my date style settings, however is there a better way for PostgreSQL to have a collection of tolerated datestyle settings instead of constantly changing it? I am still getting to terms with the settings of PostgreSQL and considering I am going to be dealing with UTC later, I'm worried about changing something I won't know how to change back. Any recommendations?

Please paste the code and error message here. It's OK to alter identifying info such as a folder name ERROR: date/time field value out of range: "4/16/17 2100 US/Eastern" HINT: Perhaps you need a different "datestyle" setting. SQL state: 22008

robml commented 2 years ago

I want to add that I opted for this as a temporary solution:

SET datestyle TO ISO,MDY

which seemed to work, however would this enact any permanent changes or would I need to change the .conf file for that?

anthonydb commented 2 years ago

Hi, @robml,

Your solution is a good one. SET changes the parameter for the current session only and is not permanent. If you wanted to make a permanent change, you would need to change the value in the .conf file.

I do not know whether PostgreSQL would allow a collection of date styles, but I will check if that is possible.

Meanwhile, documentation about SET worth reading here if you have not already seen: https://www.postgresql.org/docs/current/sql-set.html

I will make a note of this for other users whose installs are set to date styles other than MDY.