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
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
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
bcantw...@firescope.com wrote:
How would I go about modifying a unix timestamp to actually represent the
'top of the hour' that it represents?
For
= 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
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
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
Hi Keith,
I'm not sure, but this might be DST that's in your way. Have you
looked into that?
Have a nice day,
- Martijn
On Mon, Apr 20, 2009 at 18:34, Keith Hughitt keith.hugh...@gmail.com wrote:
Hi all,
Does anyone know what is going on here:
//Query:
select UNIX_TIMESTAMP(TIMESTAMP
Hi all,
Does anyone know what is going on here:
//Query:
select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:00')) as first,
UNIX_TIMESTAMP(TIMESTAMP('2003-10-05 00:00:00')) as second,
UNIX_TIMESTAMP(TIMESTAMP('2004-01-01 00:00:00')) as third;
++++
| first
timestamp into human readable date.
Obviously Access doesn't have mysql *FROM_UNIXTIME(unix_timestamp)*
function.
So: may someone know a reference to the function algorithm?
Or can someone tell me how can i use mysql functions into MSAccess?
Thank you in advance...
|
--
MySQL General Mailing
I would like an integer field to capture the current date as a Unix
Timestamp by default.
But this will not be accepted at all.
I get the error invalid default value for [field name]
Is there a workaround?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
in a table will generate the current
timestamp.
Best work around I can think of is to set your field as an int
and include unix_timestamp(NOW()) in your inserts
-Original Message-
From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 10, 2006 2:33 PM
To: mysql
An efficient way to store time is as UNIX_TIMESTAMP (4 bytes) instead of
using DATETIME data type (8 bytes). We were using this technique to save the
time in our database.
Now we need to support millisecond resolution as we need to store data
comming 8 times per second, that is, every 125
2006/2/9, Jose Antonio [EMAIL PROTECTED]:
An efficient way to store time is as UNIX_TIMESTAMP (4 bytes) instead of
using DATETIME data type (8 bytes). We were using this technique to save the
time in our database.
Now we need to support millisecond resolution as we need to store data
comming
How to select datetime using UNIX_TIMESTAMP excluding Saturday and Sunday?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT unix_timestamp(mydatetime), ... from mytable where
DAYNAME(mydatetime) is not in('Saturday','Sunday');
On Thu, 2005-02-17 at 16:34, Jerry Swanson wrote:
How to select datetime using UNIX_TIMESTAMP excluding Saturday and Sunday?
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL
and assign UNIX_TIMESTAMP() to it in your INSERT statement (the format
you want).
Note that the 10 in INT(10) is the display size, not the size of the
integer. You probably knew that, but just in case... Otherwise,
int(10) unsigned not null default 0 should be fine.
Michael
Keith Bussey wrote
Currently I am using UNIX_TIMESTAMP() in my INSERTS, just the idea came to me
that maybe I could use a MySQL function as a default for a field. Too bad
MySQL doesn't allow it though..
Thanks for your reply though =)
Oh and yeah, I knew the int(10) was for the display length ;p
--
Keith
Hello
I have a table that gets appended to every night with the LOAD DATA command.
The column 'start' is a unix timestamp. So when the LOAD DATA is done I then
want to update the Column to be human readable.
I was thinking of the following:
UPDATE table SET start = UNIX_TIMESTAMP(start);
I
08, 2003 7:26 AM
To: [EMAIL PROTECTED]
Subject: Update table with UNIX_TIMESTAMP
Hello
I have a table that gets appended to every night with the LOAD DATA command.
The column 'start' is a unix timestamp. So when the LOAD DATA is done I then
want to update the Column to be human readable.
I
Thomas Zehetbauer writes:
I mistakenly set the value of a DATETIME column to the value of
UNIX_TIMESTAMP(). I know that this is not good practice and I would have even
expected it to fail.
But what has happened was totally unexpected for me, invalid date values like
'2000-10-09 55:17:71
I mistakenly set the value of a DATETIME column to the value of
UNIX_TIMESTAMP(). I know that this is not good practice and I would have even
expected it to fail.
But what has happened was totally unexpected for me, invalid date values like
'2000-10-09 55:17:71' were inserted. As I could not set
Colin Faber writes:
Description:
When attempting to select a result set by subtracting the value
of an unsigned INT column against UNIX_TIMESTAMP() the result set
is invalid.
How-To-Repeat:
Test case:
[skip]
Thank you for your bug report.
I have been able
Hi!
Colin == Colin Faber [EMAIL PROTECTED] writes:
Description:
Colin When attempting to select a result set by subtracting the value
Colin of an unsigned INT column against UNIX_TIMESTAMP() the result set
Colin is invalid.
How-To-Repeat:
Colin Test case:
mysql create table t (ts int
Michael Widenius wrote:
Hi!
Colin == Colin Faber [EMAIL PROTECTED] writes:
Description:
Colin When attempting to select a result set by subtracting the value
Colin of an unsigned INT column against UNIX_TIMESTAMP() the result set
Colin is invalid.
How-To-Repeat:
Colin
Description:
When attempting to select a result set by subtracting the value
of an unsigned INT column against UNIX_TIMESTAMP() the result set
is invalid.
How-To-Repeat:
Test case:
mysql create table t (ts int unsigned not null); insert into t values
|
||
--
|__|__|
|| ||
ooO Ooo
-Original Message-
From: Michael Meltzer [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 10, 2001 4:15 PM
To: Theo Van Dinter; [EMAIL PROTECTED]
Subject: Re: unix_timestamp doesn't understand year 2038
-669-6892 x28990
-Original Message-
From: Dinkler, Fred [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 11, 2001 1:14 PM
To: Michael Meltzer
Cc: Mysql List (E-mail)
Subject: RE: unix_timestamp doesn't understand year 2038
UNIX version of Y2K problem
On 11-Jul-01 Dinkler, Fred wrote:
UNIX version of Y2K problem...
No, it is not, RTFM (message).
mysql select unix_timestamp(2038-01-01 00:00:00);
+---+
| unix_timestamp(2038-01-01 00:00:00
Description:
unix_timestamp doesn't understand year 2038.
How-To-Repeat:
this works:
select unix_timestamp(2037-12-31 23:59:59);
this doesn't:
select unix_timestamp(2038-01-01 00:00:00);
Fix:
unknown. everything through Jan 19 03:14:07 2038 GMT
]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 10, 2001 3:59 PM
Subject: unix_timestamp doesn't understand year 2038
Description:
unix_timestamp doesn't understand year 2038.
How-To-Repeat:
this works:
select unix_timestamp(2037-12-31 23:59:59);
this doesn't:
select unix_timestamp(2038-01-01
.
So it's not a bug that there are ~19 days in 2038 that MySQL (via
unix_timestamp) can't handle? I understand that 2^31 seconds after 1970
runs out in 2038 -- that's actually how I found this bug. A countdown
program I wrote determines how long until a given event. In real life,
2^31 (0x7fff
At 11:04 PM +0200 6/19/01, Attila Soki wrote:
| Paul DuBois [mailto:[EMAIL PROTECTED]]
| When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will
| receive the value directly, with no implicit
| ``string-to-unix-timestamp'' conversion. If you give UNIX_TIMESTAMP() a
| wrong or out
Sinisa writes:
- timestamp is saved in datetime format
- first timestamp column is silently updated on each UPDATE
What this means, of course, is that when you want to deal with your own
TIMESTAMP data, you need to maintain some sort of sacrificial column (e.g.
LAST_UPDATED TIMESTAMP) that
Shankar Unni wrote:
Sinisa writes:
- timestamp is saved in datetime format
- first timestamp column is silently updated on each UPDATE
What this means, of course, is that when you want to deal with your own
TIMESTAMP data, you need to maintain some sort of sacrificial column (e.g.
[EMAIL PROTECTED] writes:
Description:
The following line inputs the current UNIX time into a field -
date_start - with the date_stop filed defaulting to '0':
insert into contract_work (sess_id,email,date_start)
values('bde32bfde3ac89c7d510df573bb6bb88','Joe Bloggs',unix_timestamp
Hi,
Does the function Unix_TimeStamp() work in MySQL under Windows NT?
It doesn't seem to work here in simple queries, such as this query:
Select Unix_TimeStamp();
What about
SELECT UNIX_TIMESTAMP(NOW())
The above shouldn't be necessary - it is actually supposed to give you the
current
On Mon, Apr 30, 2001 at 12:24:26PM +0100, Basil Hussain wrote:
Hi,
Does the function Unix_TimeStamp() work in MySQL under Windows NT?
It doesn't seem to work here in simple queries, such as this query:
Select Unix_TimeStamp();
What about
SELECT UNIX_TIMESTAMP(NOW
Hi,
Does the function Unix_TimeStamp() work in MySQL under Windows NT? It
doesn't seem to work here in simple queries, such as this query:
Select Unix_TimeStamp();
Thanks
Noor
-
Before posting, please check:
http
On Sun, Apr 29, 2001 at 05:30:58PM +0200, Noor Dawod wrote:
Does the function Unix_TimeStamp() work in MySQL under Windows NT?
It doesn't seem to work here in simple queries, such as this query:
Select Unix_TimeStamp();
What about
SELECT UNIX_TIMESTAMP(NOW())
?
--
Jeremy D. Zawodny
39 matches
Mail list logo