Justin Staugaitis Justin Staugaitis - 6 months ago 11
SQL Question

Using SQL Commands to subtract 1 minute intervals from Date Column to return matched record

I have a database and within this DB I have a Table called "Bakery". Within this Table there are two columns, Food and Time. The first column Food represents the food that is produce and then the second column Time represnts the time the food it was made.

Example

FOOD TIME
Cookie 5:00 AM
Cookie 5:10 AM
Cookie 5:18 AM
Cookie 5:30 AM


If I want to create a SQL Command to see when the last batch of food was made to when a customer purchased a Food what would be the best way to do this.

Example


John bought a cookie at 5:17 AM when was the last batch of cookies made for John?


I want the result to return 5:10 AM.

And I have hundreds of Names and different foods so what would be the best way?

Answer

I assume that "input" values are BUYED_FOOD (cookie i.e.) and TIME_OF_BUYING (5:17 i.e.).

SELECT DISTINCT ON (FOOD) * 
FROM Bakery
WHERE 
   FOOD = 'cookie' AND 
   TIME < TIME_OF_BUYING
ORDER BY FOOD, TIME DESC

This example is general and returns whole row from Bakery. If you want to return only TIME you can replace DISTINCT part and ORDER .. DESC with MAX(TIME) like so :

SELECT MAX(TIME) 
FROM Bakery
WHERE 
   FOOD = 'cookie' AND 
   TIME < TIME_OF_BUYING