I've found story points to be terrible indicators of how long something will take once you get down to the sprint-sized chunk of work that a story should be. Velocity? It's a numbers game too. Track how many story points the team completes each sprint. It's even useful to track how many people are on the team each sprint. This doesn't predict when works gets done, but it can help justify when work doesn't get done — but that's a different question entirely.
I've found that velocity is better at predicting when large chunks of work will be done as long as your minimum unit of time is one sprint. The further out you plan, the fuzzier your prediction gets. If your team averages 24 points per two week sprint and you have a 100 point story, the naïve math says you should be done in 100/24*2 = 8.33 weeks (or 41.67 work days). And if you plan the timeline that tightly, you will be wrong.
At 24 points per sprint, a 100 point story will take longer than 4 sprints, but just barely. So, you tell management it will get done 5 sprints after it starts. When will it be deployed? Not in 41.67 days. At the end of the fifth sprint after you start.
Now the planning part comes in. This 100 point story needs to be deployed by Oct 1. Today is August 9. If, for example, this coming Monday is the first sprint you work on the story, look out 10 weeks. What date is that? Around October 18 (in the year 2024). Standing here today you can bank on being 2 weeks late.
Don't get any more fine-grained than that.
How about that 3-point story? When will that be done? In the same sprint we start it. Don't get any more fine-grained than that. If someone really wants an estimate in hours, then spend some time and estimate that 3-point story in hours. Don't hand-wave a math formula to derive hours from story points. You will be wrong. Do an honest assessment of the work ignoring all that story point mumbo jumbo.
Say you've got a backlog of stories totaling 200 points. Someone says you need total hours for that work. Those of us in the public sector frequently need to deal with that simply because some bureaucrat wrote a policy to prove tax money is being spent wisely. Don't fuss over each story. Do some naïve math, and round up to the nearest sprint: 200 / 24 = 8.33 sprints — nope, scratch that; 9 sprints. That's 18 weeks if doing a two week sprint. How many hours of labor will your team log in 18 weeks? That's your estimate in hours.
The trouble is, some people want exact numbers. They won't be satisfied with the numbers above, because they might want to know the hours for each story. At some point I just started doing naïve math and simply not caring if the numbers were wrong for each story. What is the bigger picture? Are you still on track overall? One story takes too long and now management is having a heart attack. I bet some of those stories took less time, too. Show management that it averages out over many stories. I bet they calm down a little. And sometimes management just has an axe to grind, and you just need to let them. It makes them feel better. Everyone likes to be heard, so hear them, and then move on. No need to change unless you can see the overall trend heading in the wrong direction.
That 2-point SQL script that took a week? It was easy, right? Low complexity. Low story points. This is where complexity is not a well-defined concept, in my opinion. If a simple 2 story point SQL script takes half a sprint to write, test, run, and verify, I would argue the team missed some complexity, and the points should be higher.
Why did that script take so long? That's where you need to lean on the details of the work to come up with the estimate. It isn't just the act of writing SQL that you are estimating. You need to analyze the data and tables. Maybe you need to create a rollback script. Coordinate with DBAs. Depending on the organization, you might have paperwork to submit just to get access to the database to start your analysis. What other applications are impacted? You need to notify those teams. Suddenly you're spending 4 days herding cats so you can spend an afternoon writing and debugging a SQL script. This is more complicated than you thought.
Estimating in complexity is more than the code you write. It involves all activities necessary to analyze, design, develop, test, and deploy that work. Complexity ramps up easily due to those non-coding activities. Don't forget testing, too. Every edge case potentially adds to the permutations required to ensure this "simple" SQL script works and doesn't destroy data. Oh, and that rollback script needs testing, too.
The challenge comes when you get pushback because the 30 line SQL file has a 34 point estimate. That's when you start rattling off all the non-code tasks you need to do and say it increases complexity. If nobody agrees, I'm a fan of just going with a 2 point estimate and then noting your concerns in an email or a comment on the work item. Do the thing, and lo and behold, that 2 point story took half the sprint.
That's why the sprint retrospective is essential. You can bring this story up for discussion. I bet after management made their perfect air-tight plan and watched every word you said come true that they will believe you next time. You can steer the Titanic, but not before it hits the iceberg. So state your concerns, do the work, watch it hit the iceberg, and then watch how people learn to listen to you next time.
I've been doing this for 20 years. Over this time I've noticed some patterns emerge in people's behavior regarding estimates. People demand precise estimates after a series of botched estimates and timelines. They think a more rigorous estimation process leads to more precise estimates, thereby solving the problem of missed timelines and budgets. This has the opposite effect, because so far as I know, humans don't have precognition. So we are wrong. This only serves to reinforce their belief that the estimates need to be more exact.
I've found tracking both estimates to be useful in this situation. Do the elaborate estimation process. Jump on one leg, spin three times and shout "boondoggle!" three times after the first full moon of the month. Jump through all the hoops management wants you to.
And also estimate in points and provide that fuzzy "it will be done during sprint X" estimate. Compare the two estimates with when the thing actually got done. I bet you won't need to shout "boondoggle!" after a full moon too many more times. Management will start to see the fuzzier estimate takes less time and gives them enough granularity to plan for the future.
Remember that all this talk about story points and estimates in hours is about predicting the future. You have far-off predictions and short-term predictions. They require different strategies, but you can use story points either way. Just remember that "complexity" also includes analysis, testing, and coordinating with other teams, too.
Now you can justify that 2-point SQL script being 34 points and it will be done sometime during the second sprint after you start it. And you can predict that because you track sprint velocity, which allows you to do some believable if not naïve math. It's hard to argue with numbers.