Thread: how can i get the binary format of timestamp?
Hello:
I find that the real timestamp format(got from the file which is produced by copying binary to ) is different from what
ifind in timestamp_send func.
i do think that the binary format of a timestamp "2006-04-18 11:20:20" should be "44 2B B0 6A 00 00 00 00" standing
for"secs:1143713898, usecs:0" but in fact it is:"41A7 7DBA D400 0000 "
timestamp is defined as int64 or float8, when it is defined as int64, it looks like timeval, first 32 bits for
seconds,second32 bits for usec.
and from the timestamp_send func, it just changes the first 32 bits to binary then combines the next 32 bits
/* codes from timestamp_send*/
Datum
timestamp_send(PG_FUNCTION_ARGS)
{Timestamp timestamp = PG_GETARG_TIMESTAMP(0);StringInfoData buf;
pq_begintypsend(&buf);
#ifdef HAVE_INT64_TIMESTAMPpq_sendint64(&buf, timestamp);
#elsepq_sendfloat8(&buf, timestamp);
#endifPG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}
/* codes from pq_sendint64*/
void
pq_sendint64(StringInfo buf, int64 i)
{uint32 n32;
/* High order half first, since we're doing MSB-first */
#ifdef INT64_IS_BUSTED/* don't try a right shift of 32 on a 32-bit word */n32 = (i < 0) ? -1 : 0;
#elsen32 = (uint32) (i >> 32);
#endifn32 = htonl(n32);appendBinaryStringInfo(buf, (char *) &n32, 4);
/* Now the low order half */n32 = (uint32) i;n32 = htonl(n32);appendBinaryStringInfo(buf, (char *) &n32, 4);
}
so i do think that the binary format of a timestamp "2006-04-18 11:20:20" should be "44 2B B0 6A 00 00 00 00" standing
for"secs:1143713898, usecs:0"
but in fact it is:" 41A7 7DBA D400 0000 " , i don't know why.
maybe, i have looked into a wrong func, then which is the right one?
"energumen@buaa.edu.cn" <energumen@buaa.edu.cn> writes:
> timestamp is defined as int64 or float8, when it is defined as int64, it looks like timeval, first 32 bits for
seconds,second32 bits for usec.
No, it's seconds times 1000000, not times 2^32.
regards, tom lane
Hello,Tom！ Thanks for your answer. To invoid missing something of the timestamp, i just copy the tm2timestamp func
intomy program.(Of course, some other related functions are copied, too), result is still refusing me.
What i need is: 41A7 7DBA D400 0000 stands for "2006-03-30 18:18:18" ( 41A7 7DBA D400 0000 is got from the Copy to
File)
When using tm2timestamp, result is:
if define HAVE_INT64_TIMESTAMP
result is: 4E66 E642 0030 C274
if not define HAVE_INT64_TIMESTAMP
{ if define INT64_IS_BUSTED result is: 6972 2142 0000 00DB if not define INT64_IS_BUSTED result is: 4221 7269 DB00
0000
}
So, maybe I forget to define something??
/******************************************************************************************************
* my codes
*
*/
my program is listed:
time_t time = 1143713898;
struct tm* tmField = gmtime(&time); // I know that struct tm dislikes the struct tm in "time.h"
tmField->tm_year += 1900;
tmField->tm_mon += 1; // So tmFiled is same as pg_tm
int tzp = 8;
char buffer[256];
timestamp timestampField ;
tm2timestamp(tmField,0,&tzp,×tampField);
timestamp_send(timestampField,buffer); // I changed some codes here, to get the binary format of timestamp
/*********************************************************************************************************/
so ,what switch should i turn on?
======= 2006-04-18 21:47:42 You Wrote：=======
>"energumen@buaa.edu.cn" <energumen@buaa.edu.cn> writes:
>> timestamp is defined as int64 or float8, when it is defined as int64, it looks like timeval, first 32 bits for
seconds,second32 bits for usec.
>
>No, it's seconds times 1000000, not times 2^32.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>.
= = = = = = = = = = = = = = = = = = = =
"energumen@buaa.edu.cn" <energumen@buaa.edu.cn> writes:
> if define HAVE_INT64_TIMESTAMP
> result is: 4E66 E642 0030 C274
> if not define HAVE_INT64_TIMESTAMP
> {
> if define INT64_IS_BUSTED result is: 6972 2142 0000 00DB
> if not define INT64_IS_BUSTED result is: 4221 7269 DB00 0000
> }
I think you got your cases mixed up. In the not-HAVE_INT64_TIMESTAMP
case the value would be a float8 and wouldn't be affected at all by
INT64_IS_BUSTED.
Defining both HAVE_INT64_TIMESTAMP and INT64_IS_BUSTED is not supported.
regards, tom lane