PostgreSQL Date And Time Part 1 (2021-08-09)

SQLServerCentral Newsletter for August 9, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [PostgreSQL Date And Time Part 1]( - [Top Reasons to Attend PASS Data Community Summit]( - [Understanding Hybrid Buffer Pool in SQL Server 2019]( - [From the SQL Server Central Blogs - Data Mesh: Centralized ownership vs decentralized ownership]( - [From the SQL Server Central Blogs - How do I unlock a SQL Server Login using T-SQL (without changing the password)?]( Question of the Day - [Index Type Limits]( The Voice of the DBA  Daily Coping Tip Be kind and supportive to everyone you interact with I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. How Paranoid Are You About Backups? A friend sent me [this article from Backblaze on backup strategies](. He asked me what I thought, since I do write on DR and backup strategies. I've also blogged at times on my personal strategy, which is Backblaze and cloud services. I have a subscription, that I just renewed, to ensure that my two primary desktops are backed up outside of my house. The article talks about the 3-2-1 backup strategy and then some more modern evolutions of this. If you don't know, 3-2-1 means 3 copies of your data on 2 types of media with 1 copy offsite. That's a strategy I've followed for years in enterprise environments. We usually had a local current copy of databases on disk, a second copy on tape, and multiple tapes offsite. These days there are some other variations, with 3-2-1-1-0 and 4-3-2. These might include adding air-gaps between items and verifying there aren't errors in your backups. They also include the idea that for cloud providers, you include multiple regions or countries in case your vendor has issues. Being slightly paranoid is a good thing, especially in today's environment when ransomware is such a huge issue. While some companies might choose to pay a ransom and decrypt data quickly, many start recovery efforts, with the pressure on IT staff to quickly restore backups, and hopefully not bring in another copy of the ransomware that is time activated. No matter what strategy you choose, and there are pros and cons to all of these, you need to be sure you can execute on this strategy quickly. I've run test restores from Backblaze, just to see if I can get back a random file or folder that was backed up. I've also wanted to ensure I understood how this works in the event of a disaster. I've been able to bring back a file to a different machine, which is likely the strategy I'd employ in my personal life. At work, the same idea applies. You need to test your restores, and with a large staff and constantly changing environment, you should do this more often. The tolerance for delays (RTO) and lost data (RPO) is lower at most organizations, and clients expect recovery to be sooner rather than later. I've also seen upper management (directors, VPs), show up and watch technical staff try to recover data. They don't like to see you fumbling through documentation or googling how to restore certain applications. They do expect you quickly and efficiently get the process moving. A disaster is stressful, and they will likely occur at inconvenient times, like when you're on vacation or in the middle of a big project. Having some knowledge, and some confidence in your skills, will help ensure that you can function under stress. The more confidence you have in yourself, the more others will have in you, so make time to build that confidence on a regular basis. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [PostgreSQL Date And Time Part 1]( Shivayan Mukherjee from SQLServerCentral This article shows the basics of using the age, and current date/time functions in PostgreSQL. [Top Reasons to Attend PASS Data Community Summit]( Additional Articles from Redgate With over 160+ educational sessions, networking events, speaker Q&As with leading data platform experts, Learning Pathways, and much more, there are so many reasons to attend this year's free and on-demand PASS Data Community Summit. Check out our new video to get a taste of what's in store! [Understanding Hybrid Buffer Pool in SQL Server 2019]( Additional Articles from MSSQLTips.com In this article we look at the Hybrid Buffer Pool available in SQL Server 2019 and how to enable and disable this feature for SQL Server. From the SQL Server Central Blogs - [Data Mesh: Centralized ownership vs decentralized ownership]( James Serra from James Serra's Blog I have done a ton of research lately on Data Mesh (see the excellent Building a successful Data Mesh – More than just a technology initiative for more details),... From the SQL Server Central Blogs - [How do I unlock a SQL Server Login using T-SQL (without changing the password)?]( Kenneth.Fisher from SQLStudies We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Index Type Limits Which of these data types are not allowed for the columns in an index? (choose 2) Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Default OPENJSON If I run the OPENJSON() in a SELECT without any additional clauses, as in this: SELECT * FROM OPENJSON(@d) AS oj What columns are returned by default? Answer: key, value, and type Explanation: Without a schema, the key, value, and type columns are returned. Ref: OPENJSON - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Administration [Transaction log shrink w/ logshipping]( - Hello everyone, Again with this question. I've got a log-shipped database, and I would like to decrease the size of the transaction log. What's different from the other examples of this question I've seen here is that I do know that the current size of the transaction log was caused by a single large […] [How to limit SQL Job Success events]( - Hi, We have a monitoring tool that reads Windows Event Log entries for Failure and Success of SQL Job executions. So, we have setup MSSQL jobs to write to the Windows Event Log when they fail or complete successfully. This works okay, however for jobs that run at a very high frequency I find that […] SQL Server 2016 - Development and T-SQL [Bulk Insert 4832, 7399, and 7330 errors]( - Hello all, I was wondering if someone can shed some light on my dilemma. I have a SQL job that runs a script containing a bulk insert process. Occasionally fails with the three error codes. 4832, 7399, and 7330. Executed as user: xxxx . Bulk load: An unexpected end of file was encountered in the […] [Not with delete Statement help]( - Hello There Good Morning,  Have a quick question, with delete statement right now I have the below statement. so i am trying to delete the other records that means not matching below criteria can i use NOT ? Delete from Patient WHERE (isnull(billamount,0)+isnull(discamount,0)<100 AND ( domesticpatient = 'Y' or invalidaddress = 'Y' or invalid […] Administration - SQL Server 2014 [Slow performance after database shrink]( - Hi, We performed a database shrink on both log as well as data files as a last resort to save some space. It did helped us to resolve the space issue but the DB is running tremendously slow post that. We checked for the index fragmentation and reorganize/rebuilded them accordingly. Now I am not a […] Development - SQL Server 2014 [Alternative to LAG(SUM) - Can a Column Reference The Sum of the Values Above It]( - Hi Everyone, I'm trying to do something that seems pretty simple, but SQL Server doesn't seem to allow it. For a given column - let's call it col C - I want to use the SUM of all values in col C in rows above the current row to determine the value of col C […] SQL 2012 - General [Migrate SQL Sever 2012 Database To Server 2014 and Subsequent Versions]( - Hi Guys, I have an SQL Server 2012 Express database that I would like to migrate to SQL Server 2014 Express as well as subsequent versions. The reason for wanting multiple versions of the database is because I will be developing an application using the database and I haven't decided which version of .Net I […] SQL Server 2019 - Administration [Need to change RS Execution Acct PWD, automatically w/ T-SQL]( - We use a 3rd party vendor to administer our SQL Server service account PWDS. We have an API to capture the new PWD -- which changes every "n" days and we're looking to automate the PWD update for our SSRS Execution Account. Using T-SQL, is there a way to change the SSRS Execution Account PASSWORD […] [Setting data compression on a database or even server level possible?]( - I well realize data compression is a subject to a few variables, such as table/index/both and page vs row but in case a certain data compression implementation is desired across all database tables and indexes both, all page, for example, is there a way to set this on a database or even a server level? […] SQL Server 2019 - Development [NOT IN vs. NOT EXISTS]( - This is a basic question which occurred to me while reading something on the usage of NOT IN and NOT EXISTS. It made me to go back and look into a query I wrote a few years go to compare differences. In below queries, although they both give me the same result and same processing […] [Remove table partitioning]( - Hi all, Just wanted to check something out if I may, we have a table (call it TableA) that is 29GB in size, its currently split over a number of partitions, I need to remove the partitioning and I wanted to check to see if there was another/easy way to do this, as of now […] [Having sum(amount) = 0 returns negatives as well.]( - Hello all, I'm trying to get a list of ID's that have a total sum of their amount column = to zero... But I'm getting a lot of negative numbers, but no positives. So, for example, I might get an ID that has two rows in total which if you add up their amounts it […] SQL Server 2008 - General [Storage Space of a Column having XML datatype with NULL value]( - Does XML datatype with NULL Value in SQL Server 2008 consume any storage space? As mentioned in If we store a NULL value in a fixed-length column such as a column with INT data type, the NULL value will consume the whole column length. If we store a NULL value in a variable-length column […] Amazon AWS and other cloud vendors [How is Throughput optimized HDD (st 1)]( - Hi Everyone. i am planning to shift my website to AWS  I want to know the difference between Throughput HDD (st 1) & General Purpose SSD (gp3) My website is related downloads, it has 20-30k visitors.  Will there be concurrent connections problems?  i am currently using Digital Ocean SSD Reporting Services [AVE ON GROUP TOTAL]( - I created a report with average on group totals on [Length of Stay] column. In VS, it renders properly. =Sum(Max(Fields!LengthOfStay.Value, "PTGroup"))/CountDistinct(Fields!UniqueID.Value) When I upload the report on the reporting server, I'm getting this instead: And when I export to EXCEL, this is what I get:  Any ideas why?     [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -