2

I have a huge database which includes a "posted time" field.

This field contains values such as: 2 days ago, 3 months ago, 5 minutes ago...

I can sort it the hard way which involves looking first at the second parameter (day, month, minutes) and then looking at the first parameter which is the number.

I was wondering if there is a better (easier) way?

Improve this question
3
  • 2
    You don't have a timestamp column in your huge database? Commented May 25, 2017 at 12:41
  • 2
    python or sql??? Commented May 25, 2017 at 12:42
  • 6
    Your database contains the string values "2 days ago"…?! That's not terribly useful in the long run, is it? Commented May 25, 2017 at 12:42

2 Answers 2

Reset to default
4

As mentioned in one of the comments, reconsider changing database structure (or at least this column). Whole point of comparing date is meaningful as long as you can relate it to some fixed point of time (that means - you have some kind of 'absolute' value, like epoch time).

If you not able to work on database design or there is some obscure purpose for that schema, you can check already existing PIP package:

https://pypi.python.org/pypi/dateparser

From package docs:

Features

  • Generic parsing of dates in English, Spanish, Dutch, Russian and over 20 other languages plus numerous formats in a language agnostic fashion.
  • Generic parsing of relative dates like: '1 min ago', '2 weeks ago', '3 months, 1 week and 1 day ago', 'in 2 days', 'tomorrow'.

  • Generic parsing of dates with time zones abbreviations or UTC offsets like: 'August 14, 2015 EST', 'July 4, 2013 PST', '21 July
    2013 10:15 pm +0500'.

  • Support for non-Gregorian calendar systems. See Supported Calendars.

  • Extensive test coverage.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is a a really good answer. I especially like how you highlight that the dates should be absolute if it can be helped, but provide at least a chance of converting the relative dates anyway.
Sounds good, I'll have a look at this package. This value "x time ago" is given as a string so there isn't much I can do in term of database. Thanks
1

These fuzzy values "x y's ago" are clearly display values calculated from some original source data. Are you sourcing these data from some API?

You should instead try to source the original data behind these display values. Probably the "huge database" you are sourcing these records from can be queried in a way that returns absolute values for dates, rather than these fuzzy ones.

(as an aside, I find the current trend of using so-called human-friendly fuzzy date stamps to be extremely annoying, especially when you can't turn them off. Not only does it impact screen scraping applications as this appears to be, but it's really a hindrance for time-critical data such as ticketing systems with date-stamped notes. I look forward to seeing this UI trend abate).

Improve this answer

3 Comments

"answered 1 min ago"
This trend is not for reasons of human friendliness, it's due to the so-called big data collectors (social networks et al.) not wanting to share with the world their bread and butter - the data itself. The harder you make it on automatic scrappers, the better from their perspective.
@Dan D added (and then removed) a comment here that had the actual Answer I think: probably the source data does have the raw date within the HTML with a special CSS class and it's just being displayed by a piece of javascript: ``` <div class="user-action-time"> asked <span title="2017-05-25 12:39:48Z" class="relativetime">18 mins ago</span> </div> ```

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.