The docs don't seem accurate to me. According to the page referenced by mustaccio, allballs represents 00:00:00.00 UTC. However, if that was the case, then the last column (interval) in here should be greater by one hour.

Morning

What I mean is, it's probably just 00:00:00.00 (in the local zone, I guess), not 00:00:00.00 UTC

All times in PostgreSQL are in the local time zone, including allballs. The database normalizes those times and stores them as UTC.

this doesn't seem wrong to me, or even problematic. It's a time literal.

Actually, that's not correct allballs is in UTC.

It's special.

So when I cast allballs as time, it's actually 00:00 in UTC? But how does my example work then? I mean when I convert CURRENT_TIMESTAMP to time, the time I get matches the local time zone representation of the time returned by CURRENT_TIMESTAMP. So that time is in +01. But if allballs is 00:00 in UTC, then it should be 01:00 in +01, and the resulting interval in my example should be one hour less than shown (not one hour more, that was my mistake).

one sec checking your example

ah, I think I know why you thin kthis.

::time doesn't do the timestamp math differently from timestamp without time zone, it just has a time zone with it. All times are stored as UTC.

And all computations are done in UTC.

@AndriyM see if this makes any sense. Your example is the same as this SELECT ((CURRENT_TIMESTAMP::time without time zone) - ('allballs'::time without time zone))::interval;

I mean, it's the same as SELECT ((CURRENT_TIMESTAMP::time) - ('allballs'::time))::interval;

So I guess the issue that I didn't take into account was that the time type is synonymous with time without time zone

And I can't do SELECT ((CURRENT_TIMESTAMP::time with time zone) - ('allballs'::time with time zone))::interval; because it says operator doesn't exist for that operation on those types (not on dbfiddle.uk anyway).

Thanks Evan, it all makes sense now actually.

Morning from UTC+2

Ha, morning+02, that's a new one :)

morning from UTC+1

silly question of the day: how many timezones are used in the UK?

no cheating, guess without checking the interwebs

@ypercubeᵀᴹ What do you mean by "used"? Off the top of my head, UK's summer time is called BST and it's UTC+01, and in the winter it's GMT = UTC. Is that it?

4

UTC, GMT, BST, and another one

Perhaps the question is to do with various parts of UK scattered over the world, like Gibraltar or Falklands

South Sandwich Islands, etc.

Yeah, according to timeanddate.com/time/zone/uk

> The United Kingdom has 1 standard time zone. The overseas territories and crown dependencies of the UK bring the total to 9 time zones.

and they might have missed some. I don't see them counting UTC+3 for the bases in Cyprus

oh, they use the main UK tz.

Does the UK have presence in Antarctica? If so, that one might have been missed, though I've no idea what time zone that would be.

that uses UTC+12 timeanddate.com/time/zone/@6630372

but it may not be considered part of the UK.

I guess not in the same way as a military base, no

Antarctic treaty and all that. There was some treaty in the 60s (or 70s?)

heh, we should ask our Chilean friend. from wikipedia:

> Argentina and Chile dispute the British claim, and make their own counter-claims that overlap both Britain's and each other's (see Argentine Antarctica and Chilean Antarctic Territory).

Morning

I have a collection of different HTML forms that collect and save data, each form is in a separate tab on the same page (Javascript just shows a specific form when a tab is selected). Each form in each tab are part of a single module (e.g. "User Questions") and the data collected from all forms is stored against one UserQuestions ID in the DB.

I want to allow any form in any tab to be partially saved, to allow an in "progress state". So part of a form or forms will be saved to the DB. However, this means a mandatory form field (i.e. not an empty value) can't have a database field that is "not null" as the partial state might be that mandatory field has no value yet.

I feel allowing null in a DB field that is actually not null is wrong, as then DB data integrity relies upon Model/App validation to make sure data has integrity. Any thoughts about this? Or perhaps I need some other layer that temporary stores "in progress" data?

In one of our projects, we use a separate set of tables for OLTP. Once the data is finalised, it's then transferred to a different set of tables, where it's to reside permanently. And when there's need to edit previously finalised data, again, a copy is created in the OLTP tables and then later either the permanent copy is updated or the OLTP copy is discarded, depending on whether the user chose Save or Cancel.

interesting. I guess the only drawback there is managing dupe tables. Not totally dupe as things can be invalid/null etc. But the structure would be duplicated.

definitely something I can think about though as it is a solution :)

I agree, and I'm personally not exactly happy about having duplicate structures like that. That's a matter of weighing pros and cons, as always. This setup works for us in this case. Might not work in others or not work for other people, of course.

yeah, the pro is the live data that can be used as "published" has integrity in the database

Morning all

Typed from my shiny new Model M keyboard.

Well-respected Australian national broadcaster Leigh Sales rips into Sarah Huckabee Sanders:

abc.net.au/7.30/…

Nice bit starting 2:28 where Sales asks of Trump's "it will start getting colder" statement "Was that a display of lying, ignorance or insanity?"

I don't think they'll be swapping Christmas cards this year.

@James I have two takes on this. One is to treat the DB as a souped-up key-value store for in-progress data; perhaps literally serializing that data as JSON/ XML into a blob column keyed on some session ID; perhaps having more-or-less all the "real" tables duplicated with appropriate changes, likely in their own schema.

Second is to use the proper tables but label the top-level entity as "in progress". The top level would be the user, a session, a form or whatever is that can be "in progress" and for which all those other values are held. Downsides include having everything as nullable, and having all readers check the special status.

Edit: for the serialized approach: when the user decides the data's fit to proceed that's when it is shredded and written to the real OLTP talbes (in a a single stored procedure?). Spent the first half of this year implementing exactly this approach.

Abandoned sessions are easy to clean up with the "separate store" approach, less so with the "top level status" one. Flip side is the serialized values may become stale / invalid between the time they're typed and the time they're written to real tables.

Good afternoon all. I see the same usual suspects still here

Morning

PostgreSQL 13 RC 1 Released!

Morning @gbn

Morning

Ok, my turn for fun questions: I have a repository of data that is called from various applications in an ad-hoc manner. So the current plan is: User calls script/function from their environment -> stored procedure receives information, returns a RequestId -> script/function loads data (anywhere from 10K - 1M rows) into "Request" table using the RequestId -> script/function selects data from view that handles all the joins/etc.

Question is: Should I have each function/script send a row count + checksum to manage cases where the same data is requested before the underlying data is refreshed (which is monthly)? Could save some I/O time, but on the other hand requires a little more development for the caller.

And obviously there is the issue of collisions, but given the type of data being sent the odds are probably very low.

Your definition of fun differs from what I consider fun.

wildly

Or, you have a table(s) filled with metadata about the various forms and the data being collected. Use that data to drive the creation of the nullable staging table, the finalized table, the procedure to move finalized data from stage to done and if you were feeling really wicked, build the UI elements for data collection

Seems easier to just create a fresh universe with initial conditions tuned to produce the result needed

But where's the fun in that?

Conditions of The Heap seem to be tuned appropriately

almost as if by design

Are we the finalised data or the UI elements or...?

We're dark matter. We don't exist, no one measures us but were we not here, there would be no balance

@billinkc If dark matter wasn't a thing and galaxies flew apart/stars didn't form and there was no life that would mean fewer javascript libraries

But then we'd have vbscript libraries

There's always a catch

Is suppressing vbscript libraries one of the functions of javascript libraries?

Sorting bias - javascript comes first

Why applescript didn't take over the world is a mystery

Me: I want Visio Standard. Let's see if my employer offers a discount.

MS: $280. Me: What? What's pro cost then? MS: 14.99 plus tax

So I now have a $15 copy of Visio Pro 2019, which is probably about what it's actually worth.

Capitalism is broken

See Evan's message history for details

draw.io which rebranded to diagrams.net is free, lightweight, drawing tool. And they have a thick client app for your desktop.

IT IS I, EVAN THE GREAT 🤡

> And I can't do SELECT ((CURRENT_TIMESTAMP::time with time zone) - ('allballs'::time with time zone))::interval; because it says operator doesn't exist for that operation on those types (not on dbfiddle.uk anyway).

@AndriyM That's weird. Try this \doS+ and search for where the result type is interval

pg_catalog | - | timestamp with time zone | timestamp with time zone | interval | timestamptz_mi | subtract

ah that's timestamp with time zone

It's all balls