Unsupported timestamp column

[Jasonbit]

I have a Swift 2.2 (open source, non-xcode) project that I'm trying to get working. For my varchar columns everything is working fine. However, I'm trying to figure out timestamp.

The driver seems to return a integer in bytes. For this timestamp

2016-01-24 15:36:27.223667

I get back a byte array of

[0, 1, 205, 134, 65, 17, 249, 235]

I've tried converting this to a UInt64, which I can do via the ByteSwap.typeFromByteArray function. I'm not sure if this is the right approach, tho. I'm assuming that the byte array represents a 64-bit number, but I'm not sure which. For instance, the UInt64 representation of the byte array is 17003640841246146816, Int64 representation is -1443103232463404800 (I definitely don't think it's Int64).

Further reading of the postgres docs state that all timestamps are 8-byte Julian dates.

So, my first issue is trying to figure out what data type the byte array is returning back and then second will be getting the Julian date figured out.

Any thoughts on the first? Thanks.

[Jasonbit]

For now, I'll just switch to text as the query data format since that works (albeit, not as efficient).

Ah, switching to text now gives me incorrect values for my ints.. Ok, back to the drawing board.

[stepanhruda]

If you use text, is the result output 2016-01-24 15:36:27.223667?

[Jasonbit]

Hi Stephan,

Yah, I tried that, and it did work for timestamps. But then for all my int columns (id, etc), the string representation was not correct and I couldn't figure out how to get that right.

I think the way to go is binary since all the ints and string byte arrays work. I saw comments in the code for the data types that don't work that they just get sucked into a byte array. For timestamps, I just have to figure out what that data type is..

[stepanhruda]

I'm mainly interested in what the output that you get for string is, so I can try to correlate it to the binary output.

The goal is to have binary implemented for all the different column types.

[Jasonbit]

Got it. Yes, the text output is 2016-01-24 15:36:27.223667

[stepanhruda]

I can look into full timestamp support this weekend or so. My current trail of thought in case you are interested or want to work on it yourself:

You need to swap endianness. UInt64(bigEndian: typeFromByteArray([0, 1, 205, 134, 65, 17, 249, 235], UInt64.self)) gives you a result of 507451477719531. This is a very suspicious number because in microseconds it converts to 16 years and a couple of days.

PostgreSQL documentation says that timestamps start at 4713 BC. But perhaps your column in fact starts at 2000-01-01 00:00:00?

[stepanhruda]

The main problem is – to represent a date with Swift on Linux, we need to add support for https://github.com/apple/swift-corelibs-foundation first to get access to NSDate.

[Jasonbit]

Hmm, I played around with a bunch of these permutations over the weekend. I was wondering about the big endian stuff. I also read on some psql list that one dev based his timestamp bytes off 2000-01-01, so if you get microseconds 16 years plus a few days, then that sounds pretty close. Closer than I got.

I'll give that a shot. Thx.