Convert unix time to 12:00 AM of that same day

Convert unix time to 12:00 AM of that same day

am 01.03.2011 18:17:26 von bcantwell

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=gcdmg-mysql-2@m.gmane.org

Re: Convert unix time to 12:00 AM of that same day

am 01.03.2011 18:47:14 von Claudio Nanni - TomTom

--90e6ba6136643b4a8c049d6f6307
Content-Type: text/plain; charset=ISO-8859-1

You can start by using 60*60*24=86400
;)
On Mar 1, 2011 6:17 PM, "Bryan Cantwell" 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=claudio.nanni@gmail.com
>

--90e6ba6136643b4a8c049d6f6307--

Re: Convert unix time to 12:00 AM of that same day

am 01.03.2011 18:58:48 von bcantwell

--------------060309080502070400090401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

It was of course a typo, and even with the correct number isn't the answer

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" > > 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=claudio.nanni@gmail.com
> >



--------------060309080502070400090401--

Re: Convert unix time to 12:00 AM of that same day

am 01.03.2011 19:04:51 von Johnny Withers

--0016e64c1e24034e74049d6fa2ea
Content-Type: text/plain; charset=ISO-8859-1

You could use:

CONCAT(DATE_FORMAT(FROM_UNIXTIME(1298999201),'%Y-%m-%d'),' 12:00:00')

JW

On Tue, Mar 1, 2011 at 11:58 AM, Bryan Cantwell wrote:

> It was of course a typo, and even with the correct number isn't the answer
>
>
> 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@firescope.com>> 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=claudio.nanni@gmail.com
>> >
>>
>
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e64c1e24034e74049d6fa2ea--

Re: Convert unix time to 12:00 AM of that same day

am 01.03.2011 19:24:09 von bcantwell

--------------050008020006090105010606
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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 > > wrote:
>
> That's cause there's two midnights.. use
> = 1298999201 + 86400 + (1298999201 % 86400)
>
>
>
> On Tue, Mar 1, 2011 at 12:58, Bryan Cantwell
> > wrote:
>
> It was of course a typo, and even with the correct number
> isn't the answer
>
>
> 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"
>
> > >> 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=claudio.nanni@gmail.com
> >
>
>
>
>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>


--------------050008020006090105010606--

Re: Convert unix time to 12:00 AM of that same day

am 01.03.2011 19:35:27 von bcantwell

That's closer:
SELECT
UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400);
Gives me 6:00 PM today...

On 03/01/2011 12:32 PM, Singer X.J. Wang wrote:
> SELECT
> unix_timestamp() + 86400 - (unix_timestamp() % 86400);

--


--
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: Convert unix time to 12:00 AM of that same day

am 01.03.2011 23:18:20 von Joerg Bruehe

Hi Bryan, all!


Bryan Cantwell wrote:
> That's closer:
> SELECT
> UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400);
> Gives me 6:00 PM today...

The Unix timestamp is UTC-based (old name: GMT).
You don't write which timezone you are using, but your notation "6:00
PM" makes me assume you are US-based.

Is it 6:00 PM in your timezone at UTC midnight?


Joerg

--
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

--
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