How to convert 365 days to one year in postgresql?

Question

1.00/5 (1 vote)

See more:

How do I convert 365 days to equal 1 year in PostgreSQL, in a new column within a query?

Let's say we have three columns:

1. Department
2. Employee
3. Days_of_Service

How can I create an extra column where 365 days = 1 year? I would assume this would be a float if the days were over and/or under 365 days. Feel free to explain what this process is called, I would love to better understand it for future queries.

The data in Days_of_Service is just an INT (i.e. 1 day = 1)

We can assume the original code is:

SQL

SELECT
   Department
   , Employee
   , Days_of_Service
   , SOLUTION AS years_of_service --Basically, 356 days should = 1 year in this column
From employee_list

What I have tried:

I cannot find anything about unit conversions for PostgreSQL, for this specific situation. This question was obliterated on StackOverflow.

Posted 7-Jun-21 14:59pm

tdelozie

Updated 7-Jun-21 19:37pm

v2

Add a Solution

Comments

Richard Deeming 8-Jun-21 4:11am

NB: You are ignoring leap-years, and your current database structure has no way to account for them.

Depending on your actual requirements, it may be better to store the employee's start date in the table, and then calculate the days/years of service on demand.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2021-06-07T19:37:00

Solution 1

SELECT
   Department
   , Employee
   , Days_of_Service
   , Days_of_Service/365 AS years_of_service
   , CAST(Days_of_Service AS float)/365 AS years_of_service_float
From employee_list

Posted 7-Jun-21 19:37pm

RickZeeland

Updated 7-Jun-21 19:43pm

v2