1

In Sails ORM/ODM Waterline there 3 date types: date, time and datetime. In my model I have type:'date'. But when i tried to save date like YYYY-MM-DD, it saved it like YYYY-MM-(DD-1)T22:00:00Z(UTC). Database: postgreSQL

How to keep only date, without time and timezone conversion? Of course i can keep it as string type. But in this case i wouldn't be able to search date in range.

Update 1: This is part of my model. 

id: {type: "Integer", autoIncrement: true, primaryKey: true},
name: {type: "string", required: true,},
description: {type: "text", required: false,},
type: {type: 'string'},
startDate: {type: "date",},
endDate: {type: "date",},
episodes: {type: "Integer", required: false,}

This is inside of script "Create ..." 

  id serial NOT NULL,
  name text,
  description text,
  type text,
  "startDate" date,
  "endDate" date,
  episodes integer,
  "createdAt" timestamp with time zone,
  "updatedAt" timestamp with time zone,

I don't need fields created and updated either(which auto added to every table), but this might somewhere disables in config

kseon12
  • 41
  • 6
  • Sounds as if the column was created as `timestamp` in your database. Show us the `create table` statement. –  Oct 13 '15 at 14:05
  • You absolutely can search a date range when your dates are saved as strings with the format of `YYYY-MM-DD`. It may not be best practice, but I have many legacy DB stores in Postgres and MySQL that perform very fast date calculations when the date is a simple string. I also have a preference for this because I know my dates will transform exactly the same no matter the DB, the adapter or the framework consuming the information. Same reason I use INTs for my date/time objects as well. – Meeker Oct 13 '15 at 14:21
  • @Meeker can you place some example how you do that ? – kseon12 Oct 13 '15 at 17:19
  • If you store a value in a `timestamp` then a time part **will** be stored as well. On which column do you have that problem? `date` columns will **never** store a time or a timezone - if you see something like that it's your *application* (or your SQL client) that displays this. @meeker: all modern DBMS store dates in a very efficient manner and I very much doubt that you could measure a performance difference between an integer and a real date (or timestamp) when doing calculations. Plus you can be sure that only valid dates are stored (at least in Postgres) –  Oct 13 '15 at 18:21
  • No trick to it. In sails its just a string. You search just as if it were a string. There is more you could do with validations and DB performance, but it is that simple. I'm not saying it is your solution (thus not putting it in the answer section), but your assumption in that you can't do it was wrong. @a_horse_with_no_name I agree that integer vs date will have no performance difference (I never said it would), I do it for the ubiquity of the datatype. An INT is an INT the world around. – Meeker Oct 13 '15 at 21:07
  • @a_horse_with_no_name oh, you are right. It's stored as it should and have valid values. Sails.log was showing improper things, but it's another problem – kseon12 Oct 13 '15 at 22:53
  • @Meeker but you didn't explain how to search between 2 string dates. I mean, i want to find all entities with date between 1995 and 2005 year. And i can't imagine how to do this with `type: string` and SQLQuery – kseon12 Oct 13 '15 at 22:59
  • Its the same as any SQL query. `SELECT * FROM db.table WHERE myDateAsString >= '1995-01-01' AND myDateAsString < '2006-01-01'` – Meeker Oct 14 '15 at 00:29
  • in waterline it would something like `where: { 'myDateString' : {'and' : {'>': '1995-01-01', '<' : '2006-01-01' }} }` – Meeker Oct 14 '15 at 00:33
  • @Meeker, but why is it possible, since it's a string ? – kseon12 Oct 14 '15 at 08:55
  • Magic . . . what you asking is equivalent of asking why "2 + 2 = 4". The comments section of SO is no place to describe why the world works the way it does. You might want to read a basic book on data storage and database design. – Meeker Oct 14 '15 at 13:21
  • @Meeker, i can't find where. Can you point me ? And in the end there particularly no difference using date or sting types as date storage ? – kseon12 Oct 14 '15 at 22:58
  • Oh yes, their are differences. I don't mean to suggest a string is better than date. Just that it is a valid approach. Just google a for Postgres tutorial. – Meeker Oct 14 '15 at 23:10

0 Answers0