How to round minute in SQL (Databricks) or PostgreSQL to nearest hour

To round minute to nearest hour you have to use: date_trunc('hour', 'YOUR DATE_TIME' + interval '30 minute')

DATE_TRUNC function rounds to the hour that appears in the string, so if we add +30 minutes when you have a HH:30+, this should round to next hour and when you have HH:-30 this will round to the hour that appears in your string.

So if hour is: '2022-11-21 00:05:00', the new time will be: '2022-11-21 00:00:00'

But if hour is: '2022-11-21 00:55:00', the new time will be: '2022-11-21 01:00:00'

Between 0 and 30 minutes: Rounds to the same hour in your date_time Between 31 and 59 minutes: Rounds to the next hour based on your date_time

Comments

Post a Comment

Popular posts from this blog

How to send a list by email in python using Mime Application in Databricks

Image
We have a list: ListA = ['a', 'b', 'c', 'd', 'e'] And we want to send it by email to someone. So what we have to do, first, is save this list as txt file in a temporary location in databricks, and then we open it and put it in a variable, so we can be able to send it by email. Like the example: import boto3 import smtplib import matplotlib.pyplot as plt from email import encoders from email.mime.text import MIMEText from email.mime.image import MIMEImage from email.mime.multipart import MIMEMultipart from email.mime.application import MIMEApplication server = smtplib.SMTP('smtp-mail.outlook.com:587') #i am using Outlook server.ehlo() server.starttls() server.login('email@outlook.com.br', 'password@97') #Here is your id and password sender = "email@outlook.com.br" #here is your id again recipient = ['destiny@outlook.com.br'] #here the email id from who gonn...
Read more