SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?

MSSQL Database

@dapeng has some posts on “History of Today” which reveals some old posts that were published at the same time (but not this year apparently) on steemit. It can be achieved using the following SQL.

1
2
3
4
5
6
7
8
9

select top 10 *
from 
    Comments
where
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and 
    FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
    title <> ''
order by 
    total_payout_value desc

select top 10 *
from 
    Comments
where
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and 
    FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
    title <> ''
order by 
    total_payout_value desc

Let’s explain this line by line.

• select top 10 * Choosing the first 10 posts
• from Comments Table Comments holds posts and comments
• FORMAT(created,’MM-dd’,’en-us’) = FORMAT(GetDate(),’MM-dd’,’en-us’) The Month and Day should be the same as today
• FORMAT(created,’yyyy’,’en-us’) <> FORMAT(GetDate(),’yyyy’,’en-us’) But it can’t be this year.
• title <> ” Filter out comments, which have empty titles.
• order by total_payout_value desc Show the most earned posts first.

We could also add other conditions, such as categories = ‘cn’ if we are only interested in the CN posts.

@arcange suggested the following improved version:

1
2
3
4
5
6
7
8
9
10

SELECT TOP 10 
    *
FROM
    Comments (NOLOCK)
WHERE
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us')
    AND YEAR(created) <> YEAR(GETUTCDATE()) and
    AND depth = 0
ORDER BY
    total_payout_value DESC

SELECT TOP 10 
    *
FROM
    Comments (NOLOCK)
WHERE
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us')
    AND YEAR(created) <> YEAR(GETUTCDATE()) and
    AND depth = 0
ORDER BY
    total_payout_value DESC

Some things to care about:

• Never forget to add (NOLOCK) after each table name
• total_payout_value is not an indexed field (can slow down performances)
• use GETUTCDATE() rather than GETDATE() because all time stamps in the database are UTC
• avoid returning ALL FIELDS from the `Comments` table because this can be a bunch of data
• The (nolock) is important to avoid dead-locking SteemSQL’s Database Injector process and to improve performances (to avoid killing poorly written queries)

You may also like: STEEM SQL 系列之 历史上的今天怎么实现的？

STEEM SQL Tutorial

• SteemSQL Tutorial: How to Get Random Posts on SteemIt?
• SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?
• SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?
• SteemSQL Tutorial: How to Calculate Monthly Income on STEEMIT?
• SteemSQL Tutorial: Can we Really Recover Deleted Comments/Posts on STEEMIT?
• SteemSQL Tutorial: What are the Outgoing Votes for Big Whales?
• SteemSQL Tutorial: Count Total Interests Sent
• SteemSQL Tutorial: Finding Inactive Steemians that You Follow
• SteemSQL Tutorial: How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?
• SteemSQL Tutorial: How to Get the Most Payout Authors in History?
• SteemSQL Tutorial: Get Most Single Payout Authors
• SteemSQL Tutorial: How to Avoid SQL Injection?
• SteemSQL Tutorial: I have spent 800 SBD (7000+ USD) buying votes!
• SteemSQL Tutorial: How to Check If SteemSQL is Synchronized with Steem Blockchain?

–EOF (The Ultimate Computing & Technology Blog) —