Get today's posts

Hello 

Am trying to count today's posts  it returns 0 every time
Here is my code:

PHP Code:

public function today_post(){
    $datebytimezone = new DateTime("now", new DateTimeZone('UTC'));
    $date = $datebytimezone->format('Y-m-d');
    $query = $this->db->get_where('post', array('posted_at' => $date, 'post_closed' => 0));
    return $query->num_rows();
} 


 
Thank in advance.

And how does your database look?

And how does your database look?

Code:

GO

/****** Object:  Table [dbo].[post]    Script Date: 2019/09/17 22:00:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[post](
    [post_id] [bigint] IDENTITY(1,11) NOT NULL,
    [user_id] [bigint] NOT NULL,
    [post] [nvarchar](max) NULL,
    [posted_at] [datetime] NOT NULL,
    [comment_count] [int] NOT NULL,
    [like_count] [int] NOT NULL,
    [privacy] [varchar](30) NOT NULL,
    [post_closed] [bit] NOT NULL,
    [image] [varchar](max) NULL,
CONSTRAINT [PK_post] PRIMARY KEY CLUSTERED
(
    [post_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[post]  WITH CHECK ADD  CONSTRAINT [FK_post_user] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO

ALTER TABLE [dbo].[post] CHECK CONSTRAINT [FK_post_user]
GO

dumped data is like this

post_id    user_id    post    posted_at    comment_count    like_count    privacy    post_closed    image
1    5    test    2019-07-26 20:23:08.000    0    0    public    1    
12    5    test 2 #me @nthethe5933    2019-07-26 21:42:12.000    0    0    public    1    assets/posts/nadine-shaabana-804126-unsplash.jpg
23    7    first last    2019-07-26 23:17:53.000    0    0    public    1    
34    5    test to delete    2019-07-31 00:03:28.000    0    0    public    1    
45    5    ok deleting is fun    2019-07-31 00:22:53.000    0    0    public    1    
56    5    Hello world    2019-07-31 00:41:50.000    0    0    public    1    
67    7    ðŸÂ Hey son be healthy 🍎    2019-07-31 00:46:21.000    0    0    public    0    apple_drops_spray_shadow_112225_1280x720.jpg
78    5    test del agn    2019-07-31 00:49:20.000    0    0    public    1    
89    5        2019-08-07 07:31:48.000    0    0    public    1    assets/posts/eye-for-ebony-399310-unsplash.jpg
100    5    Test this    2019-08-07 07:33:20.000    0    0    public    1    
111    5    Hello world    2019-08-07 07:41:17.000    0    0    public    1    
122    5    #FightForAfrica @nthethe5933    2019-08-07 07:43:52.000    0    0    public    0    kal-visuals-644747-unsplash.jpg
133    5        2019-08-07 08:45:37.000    0    0    public    1    amanda-jones-1277189-unsplash.jpg
166    5    ????
Stuck them
?????
???    2019-08-08 16:07:10.000    0    0    public    1    
177    5    ðŸŒworld    2019-08-08 16:40:12.000    0    0    public    1    382694-large-sniper-wallpaper-1920x1080-windows-xp.jpg
188    5    ðŸ‡ðŸ’🍓🍎🍐  #Healthy is good.. @nthethe5933    2019-08-08 18:01:31.000    0    3    public    0    elena-g-L50azdkALCA-unsplash.jpg
199    5    ????Hooooohaaaa
Ooo lala?    2019-08-08 18:12:13.000    0    0    public    1    
210    7    ❤️?? Happy coding    2019-08-08 19:29:05.000    0    1    public    1    
221    5    ?    2019-08-08 20:41:59.000    0    0    public    1    
232    7    ???????Well it works    2019-08-09 02:15:48.000    0    0    public    1    
243    7    #itsworking 😄😂😂🎉 so cool..    2019-08-09 02:26:38.000    1    1    public    0    IMG_20150322_00152218.jpg
254    5    ♈️♻️♊️??    2019-08-09 06:38:32.000    0    0    public    1    
265    5    ðŸ˜‚😂😂😂😂 😅    2019-08-11 01:16:09.000    0    1    public    0    cover-image.jpg
276    5    ??    2019-08-12 02:08:28.000    0    0    public    1    assets/posts/download.png
287    5    ?❤️ #tag @topollo57    2019-08-15 05:52:13.000    0    0    friendsonly    1    assets/posts/mink-mingle-1381133-unsplash.jpg
298    11    ðŸŽ‰My first post 😁👌😋☀️    2019-08-20 08:47:42.000    0    0    public    0    smoke_shroud_clot_126551_1280x720.jpg
309    11    ðŸŽ‰ðŸŽŠðŸŽ‹ðŸŽˆFun fun fun    2019-08-20 08:49:09.000    2    0    public    0    
320    13    #protect @siphe4573 Happy time 😂👍    2019-08-20 08:57:23.000    0    0    public    0    
331    13    #coolcat    2019-08-20 08:58:21.000    0    0    public    1    cat_black_blueeyed_130470_1280x720.jpg
342    15    #️⃣??♻️?☔️
??????
??????    2019-08-20 09:04:46.000    0    0    public    0    
353    15    ??????    2019-08-20 09:14:21.000    0    0    public    0    brooke-lark-08bOYnH_r_E-unsplash.jpg
364    15        2019-08-20 09:15:29.000    0    0    public    0    kwang-mathurosemontri-fY1ECB1RCd0-unsplash.jpg
375    5    Good test for me life 😂😝 great    2019-08-20 11:02:41.000    0    0    public    0    0_TFmFug4zhzfbKsd5.jpg
452    20021    ðŸ™ŠðŸ™ˆ    2019-09-09 02:23:49.000    0    1    public    0    brooke-lark-08bOYnH_r_E-unsplash.jpg
496    5    ðŸ˜˜ðŸ˜¡â¤ï¸â˜”️🐻🚘🍻
🎉 🎊🎋🎁💝🐐🐘
    2019-09-10 09:10:08.000    0    1    public    0    0
507    5    ðŸ™hello    2019-09-10 10:47:40.000    0    0    public    1    0
518    20021    i can upload text and images now 🙊🔥    2019-09-10 11:04:00.000    1    0    public    0    eye-for-ebony-399310-unsplash.jpg
529    20021    ðŸ˜¨ðŸ˜±    2019-09-10 11:08:56.000    2    1    public    0    281836-gaming-wallpaper-1920x1080-macbook.jpg
540    20021        2019-09-10 21:22:55.000    0    0    public    1    382682-most-popular-sniper-wallpaper-1920x1200.jpg
595    11        2019-09-14 13:46:10.000    0    0    public    1    9cf65c5b223ef81141a9dc61b2d2e398.jpg
606    13    test today posted posts    2019-09-17 15:15:22.000    0    0    public    0    0

on the datetime field i inset a date and time  for uct timezone like 

PHP Code:

$datebytimezone = new DateTime("now", new DateTimeZone('UTC') );

    $date = $datebytimezone->format('Y-m-d H:i:s'); 


Doesn't TimeStampDiff  approach help?

PHP Code:

->where('TIMESTAMPDIFF(DAY, start_date, now()) <= 180') 


This example shows items which were published in the last 180 days = 6 months here.

I use this for membership expirations. If member's period is more than 6 months, that member cannot see videos or any content etc.

Doesn't TimeStampDiff  approach help?

PHP Code:

->where('TIMESTAMPDIFF(DAY, start_date, now()) <= 180') 


Thanks it works. 
Is it possible to change the 180 so that it shows posts  for today only ?
i changed 180 to 24 it shows me many posts than expect, i just want today's posts and since i'm using sql server i changed TIMESTAMPDIFF to DATEDIFF

As I arranged it for Days probably you got results for the last 24 days . 

Change it to 1, and check the output please.

(another option: for Hour you must write Hour, mine was for DAY.)

As I arranged it for Days probably you got results for the last 24 days . 

Change it to 1, and check the output please.

(another option: for Hour you must write Hour, mine was for DAY.)

sql server doesn't have HOUR they only have MONTH, DAY etc
but i think DAY is correct(https://docs.microsoft.com/en-us/sql/t-s...erver-2017) but i have to fix it correctly let me try.

Here are a couple of ways you can write that SQL. If you can, you should write them as plain SQL, it's faster.

PHP Code:

public function today_post(){
    $datebytimezone = new DateTime("now", new DateTimeZone('UTC'));
    $date = $datebytimezone->format('Y-m-d');
    $query = $this->db->get_where('post',
        array(
            'posted_at >=' => "{$date} 00:00:00",
            'post_closed' => 0
        )
    );
    return $query->num_rows();
}

public function today_post(){
    $query = $this->db->query('SELECT * FROM `post` WHERE `post_closed` = 0 AND DATE_FORMAT(`posted_at`, "%Y-%m-%d") = CURDATE()');
    return $query->num_rows();
}

public function today_post(){
    $query = $this->db->query('SELECT * FROM `post` WHERE `post_closed` = 0 AND `posted_at` >= TIMESTAMP(CURDATE())');
    return $query->num_rows();
} 


Here are a couple of ways you can write that SQL. If you can, you should write them as plain SQL, it's faster.

PHP Code:

ok thanks @jreklund let me try 


---

Here are a couple of ways you can write that SQL. If you can, you should write them as plain SQL, it's faster.

public function today_post(){
    $datebytimezone = new DateTime("now", new DateTimeZone('UTC'));
    $date = $datebytimezone->format('Y-m-d');
    $query = $this->db->get_where('post',
        array(
            'posted_at >=' => "{$date} 00:00:00",
            'post_closed' => 0
        )
    );
    return $query->num_rows();
}

This one works the others are have CURDATE() in which i get an error like curdate() not recofnized as an intenally or inbuld function.
Thank you so much @jreklund as always, you a legend.

That's weird. What database engine and version are you using? I have only tested it with MySQL.