Click here to Skip to main content
15,436,928 members
Please Sign up or sign in to vote.
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
Updated 7-Jun-21 19:37pm
v2
Comments
Richard Deeming 8-Jun-21 4:11am     CRLF
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

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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900