Convert unix time to 12:00 AM of that same day
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=arch...@jab.org
Re: Convert unix time to 12:00 AM of that same day
You can start by using 60*60*24=86400 ;) On Mar 1, 2011 6:17 PM, Bryan Cantwell bcantw...@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.na...@gmail.com
Re: Convert unix time to 12:00 AM of that same day
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 bcantw...@firescope.com mailto:bcantw...@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.na...@gmail.com
Re: Convert unix time to 12:00 AM of that same day
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 bcantw...@firescope.comwrote: 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 bcantw...@firescope.commailto: bcantw...@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.na...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Convert unix time to 12:00 AM of that same day
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 w...@singerwang.com mailto:w...@singerwang.com wrote: That's cause there's two midnights.. use = 1298999201 + 86400 + (1298999201 % 86400) On Tue, Mar 1, 2011 at 12:58, Bryan Cantwell bcantw...@firescope.com mailto:bcantw...@firescope.com 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 bcantw...@firescope.com mailto:bcantw...@firescope.com mailto:bcantw...@firescope.com mailto:bcantw...@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.na...@gmail.com -- The best compliment you could give Pythian for our service is a referral.
Re: Convert unix time to 12:00 AM of that same day
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=arch...@jab.org
Re: Convert unix time to 12:00 AM of that same day
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.bru...@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=arch...@jab.org
