List: General Discussion « Previous MessageNext Message » From: Bryan Cantwell Date: March 1 2011 6:24pm Subject: Re: Convert unix time to 12:00 AM of that same day View as plain text
```SELECT
unix_timestamp() + 86400 + (unix_timestamp() % 86400);
currently gives me 6:39 AM tomorrow....

SELECT
UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(1299003702)));
actually gives me what I want, but seems really stupid way of getting
something that is probably very simple

On 03/01/2011 12:03 PM, Singer X.J. Wang wrote:
> http://en.wikipedia.org/wiki/Midnight
>
> On Tue, Mar 1, 2011 at 13:00, Singer X.J. Wang <wang@stripped
> <mailto:wang@stripped>> wrote:
>
>     That's cause there's two midnights.. use
>     = 1298999201 + 86400 + (1298999201 % 86400)
>
>
>
>     On Tue, Mar 1, 2011 at 12:58, Bryan Cantwell
>     <bcantwell@stripped <mailto:bcantwell@stripped>> wrote:
>
>         It was of course a typo, and even with the correct number
>
>
>         On 03/01/2011 11:47 AM, Claudio Nanni wrote:
>
>
>             You can start by using 60*60*24=86400
>             ;)
>
>             On Mar 1, 2011 6:17 PM, "Bryan Cantwell"
>             <bcantwell@stripped <mailto:bcantwell@stripped>
>             <mailto:bcantwell@stripped
>             <mailto:bcantwell@stripped>>> wrote:
>             > I'd asked before how to convert a unix timestamp to the
>             hour that it is
>             > in (and got the perfect answer) :
>             > 1298999201 = 3/1/2011 11:06:41 AM
>             > (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
>             >
>             > Now getting the timestamp converted to midnight of that
>             same day isn't
>             > as simple as:
>             > 1298999201 - (1298999201 % 85400)
>             > That just gives me a unix time from yesterday...
>             >
>             > How can I convert 1298999201 (3/1/2011 11:06:41 AM) to
>             1298959200
>             > (3/1/2011 12:00:00 AM)?
>             >
>             >
>             > --
>             > MySQL General Mailing List
>             > For list archives: http://lists.mysql.com/mysql
>             > To unsubscribe:
>             http://lists.mysql.com/mysql?unsub=1
>             >
>
>
>
>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>

```