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:

   , 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.
Updated 7-Jun-21 19:37pm
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

   , 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

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