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 [at] m.gmane.org

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 [at] 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 [at] m.gmane.org

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
<bcantwell [at] firescope.com> 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 [at] gmail=
..com
>
>



--
=A0- michael dykman
=A0- mdykman [at] 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 [at] m.gmane.o rg

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 [at] now:=now(), [at] foo:=unix_timestamp(); select [at] now, [at] foo, [at] foo
- minute( [at] now) * 60 - second( [at] now) as hour_unix, from_unixtime( [at] foo -
minute( [at] now) * 60 - second( [at] now));
Query OK, 0 rows affected (0.00 sec)

+---------------------+------------+------------+----------- ---------------------------------------------+
| [at] now | [at] foo | hour_unix | from_unixtime( [at] foo -
minute( [at] now) * 60 - second( [at] 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 [at] m.gmane.org

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 [at] 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 [at] m.gmane.org

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 [at] m.gmane.org