Get date from unix_timestamp only up to the hour

How would I go about modifying a unix timestamp to actually represent
the 'top of the hour' that it represents?
For instance:
1296158500 = 1/27/2011 2:01:40 PM
That is in the 2:00 pm hour, how can I find that out and modify it to
1296158400 which = 1/27/2011 2:00:00 PM?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get date from unix_timestamp only up to the hour

If the timestmp is in seconds, the result is simply mod(timestamp,3600)

- michael dykman

On Thu, Feb 24, 2011 at 11:41 AM, Bryan Cantwell
wrote:
> How would I go about modifying a unix timestamp to actually represent the
> 'top of the hour' that it represents?
> For instance:
> 1296158500 =3D 1/27/2011 2:01:40 PM
> That is in the 2:00 pm hour, how can I find that out and modify it to
> 1296158400 which =3D 1/27/2011 2:00:00 PM?
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Get date from unix_timestamp only up to the hour

Bryan,

Maybe something like this would work?

select 1296158500 - (1296158500 % 3600)


Hope that helps,

Nathan

On Thu, Feb 24, 2011 at 08:41:58AM -0800, Bryan Cantwell wrote:
> How would I go about modifying a unix timestamp to actually represent
> the 'top of the hour' that it represents?
> For instance:
> 1296158500 = 1/27/2011 2:01:40 PM
> That is in the 2:00 pm hour, how can I find that out and modify it to
> 1296158400 which = 1/27/2011 2:00:00 PM?
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=nsullivan@cappex.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get date from unix_timestamp only up to the hour

On 02/24/2011 05:41 PM, Bryan Cantwell wrote:
> How would I go about modifying a unix timestamp to actually represent
> the 'top of the hour' that it represents?
> For instance:
> 1296158500 = 1/27/2011 2:01:40 PM
> That is in the 2:00 pm hour, how can I find that out and modify it to
> 1296158400 which = 1/27/2011 2:00:00 PM?

something like this:

mysql> set @now:=now(), @foo:=unix_timestamp(); select @now, @foo, @foo
- minute(@now) * 60 - second(@now) as hour_unix, from_unixtime(@foo -
minute(@now) * 60 - second(@now));
Query OK, 0 rows affected (0.00 sec)

+---------------------+------------+------------+----------- ---------------------------------------------+
| @now | @foo | hour_unix | from_unixtime(@foo -
minute(@now) * 60 - second(@now)) |
+---------------------+------------+------------+----------- ---------------------------------------------+
| 2011-02-24 18:06:24 | 1298567184 | 1298566800 | 2011-02-24 18:00:00
|
+---------------------+------------+------------+----------- ---------------------------------------------+
1 row in set (0.00 sec)



t

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get date from unix_timestamp only up to the hour

Yes perfect! Thanks, I knew I was over thinking this.

On 02/24/2011 10:56 AM, Nathan Sullivan wrote:
> Bryan,
>
> Maybe something like this would work?
>
> select 1296158500 - (1296158500 % 3600)
>
>
> Hope that helps,
>
> Nathan
>
> On Thu, Feb 24, 2011 at 08:41:58AM -0800, Bryan Cantwell wrote:
>> How would I go about modifying a unix timestamp to actually represent
>> the 'top of the hour' that it represents?
>> For instance:
>> 1296158500 = 1/27/2011 2:01:40 PM
>> That is in the 2:00 pm hour, how can I find that out and modify it to
>> 1296158400 which = 1/27/2011 2:00:00 PM?
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=nsullivan@cappex.com
>>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Get date from unix_timestamp only up to the hour

On 02/24/2011 05:56 PM, Nathan Sullivan wrote:
> Bryan,
>
> Maybe something like this would work?
>
> select 1296158500 - (1296158500 % 3600)

ah, yes, even this one:

mysql> select now() - interval (unix_timestamp() % 3600) second;
+---------------------------------------------------+
| now() - interval (unix_timestamp() % 3600) second |
+---------------------------------------------------+
| 2011-02-24 18:00:00 |
+---------------------------------------------------+
1 row in set (0.00 sec)

t

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org